excel - VBA Insert Rows to bottom of specific table with formula

Question

I have a spreadsheet with multiple tabs and each tab has a table. I want a button per tab to insert rows to the bottom of that table (by not having to select a cell) and copy the formulas from the row above it. Here is what I have:

-ask how many rows to insert -default insert is 1 -otherwise, use that variable to insert at bottom and copy formulas

Sub InsertRows()
   Dim i As Long
   Dim j As Variant
   j = InputBox("How many rows would you like to add?", "Insert Rows")
   If j = "" Then
      j = 1
   End If
   For i = 1 to j
      Dim newrow As ListRow
      Set newrow = tbl.ListRows.Add
      With newrow.Range
         .Offset(-1).Copy
         .Cells(1).PasteSpecial xlPasteFormulas
         Application.CutCopyMode = False
      End With
    Next
End Sub

I'm not sure how to implement the i and j from the input into the For loop.

Answer 1

This is my approach:

• Add a generic procedure to add rows to a table
• Rows are added by resizing the table range
• Add macros for each button specifying tables' names in each

Macros: Replace Table1 and Table2 with table names of each sheet

' Macros to associate with buttons
Public Sub InsertRowsInTable1()
    InsertRowsInTable "Table1"
End Sub

Public Sub InsertRowsInTable2()
    InsertRowsInTable "Table2"
End Sub

Generic code:

' Generic procedure to add table rows
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