I have a spreadsheet with multiple tabs and multiple tables on each tab. I want to add a button that adds a row to the bottom of a table and shifts the worksheet down so that the table doesn't run into the tables below. I have used a generic code found somewhere else on stack to add a row and then assigned it to a button, and it worked beautifully, but want to include a function to add a row to the whole sheet below the table. What do I add to the code to shift the sheet down? Thanks in advance!
Private Sub InsertRowsInTable(ByVal targetTableName As String)
' Ask user how many rows to ask
Dim rowsToAdd As Variant
rowsToAdd = InputBox("How many rows would you like to add?", "Insert Rows", 1)
' If user didn't input anything, default to 1
If rowsToAdd = vbNullString Then rowsToAdd = 1
Dim targetTable As ListObject
Set targetTable = Range(targetTableName).ListObject
' Resize the table to add rows
targetTable.Resize targetTable.Range.Resize(targetTable.Range.Rows.Count + rowsToAdd)
End Sub```
Thanks everyone for your responses!
I solved it with a combination of Elio Fernandes and Vincent G's suggestion.
I added For rowinsert = 0 To rowsToAdd - 1
when resizing my table. So what I ended up with was
Private Sub InsertRowsInTable(ByVal targetTableName As String)
' Ask user how many rows to ask
Dim rowsToAdd As Variant
rowsToAdd = InputBox("How many rows would you like to add?", "Insert Rows", 1)
' If user didn't input anything, default to 1
If rowsToAdd = vbNullString Then rowsToAdd = 1
Dim targetTable As ListObject
Set targetTable = Range(targetTableName).ListObject
Dim rowinsert As Integer
' Resize the table to add rows
For rowinsert = 0 To rowsToAdd - 1
targetTable.Range.EntireRow(targetTable.Range.Rows.Count + 1).Insert
Next
targetTable.Resize targetTable.Range.Resize(targetTable.Range.Rows.Count + rowsToAdd)
End Sub
I could then assign buttons using
Public Sub InsertRowsInTable1()
InsertRowsInTable "Table1"
End Sub
This let me add rows to my table and then shift everything down so the tables didnt run into each other.
Thanks everyone again for your responses!