VBA Insert Rows to bottom of specific table with formula

user_11610155 0
Viewed 1323

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.

copy-paste vba excel
2 Answers

Link your buttons to this macro which should be pasted in Module or ThisWorkbook in VBA Editor

Sub InsertRows()

Dim ws As Worksheet: Set ws = ThisWorkbook.ActiveSheet
Dim i As Long, x As Long
Dim Tbl As ListObject
Dim NewRow As ListRow

i = Application.InputBox("How many rows would you like to add?", "Insert Rows", 1, Type:=1)
Set Tbl = ws.ListObjects(1)

For x = 1 To i
    Set NewRow = Tbl.ListRows.Add(AlwaysInsert:=True)
Next x

End Sub

Using Application.InputBox lets you set a default (1) and set a variable type (Type:= 1 = numeric) which will help with data validation by not allowing non-numeric inputs.

Note this does not cover all edge cases so you still may want to add some data validation: for instance, someone could provide a negative number. In that case, 0 rows would be created. User could also provide decimals in which case excel will round to nearest whole number to determine number of rows to add. You may want to limit the input to positive integers as a result

user_67064190

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
user_15215790
Related