VBA copy row in tables down/specific order of adjustment

McGuilliam

New Member
Joined
Oct 23, 2020
Messages
20
Office Version
  1. 2016
Platform
  1. Windows
I have a excel 2016 file with 13 tables on a sheet. The formulas in the 12 last tables are depended to the first table. Now I'm trying to create a macro to add a number of rows to all tables at once. I got this to work, but it is not starting with the first table. this creates a "gap" in the references, since the row in table 1 is only added later. I would like to notice it seems no solution to me to code with the table numbers because I need the code to work for other worksheets with other tables as well. I think the best way to solve this is by selecting an earlier row of a table and copying it to the last one, but I'm not sure on how to get this done. An alternative solution could of course be to influence the order of adjustment of the tables, but to me this seems unachievable. The code I got so far to insert the rows is:

VBA Code:
Sub Test()

  Dim Table As ListObject
    Dim NewRow As Range
    Dim i As Integer
    
    Answer = InputBox("How many rows do you want to add?")
Number = CInt(Answer)
    
    For i = 1 To Number
    
    Set Sheet = ActiveSheet
    
    For Each Table In Sheet.ListObjects
          Table.ListRows.Add
    Next Table
    
    Next i

End Sub

I'm rather new to VBA, so any help would be appreciated.

Thank you.
 

Some videos you may like

Excel Facts

How to fill five years of quarters?
Type 1Q-2023 in a cell. Grab the fill handle and drag down or right. After 4Q-2023, Excel will jump to 1Q-2024. Dash can be any character.

NoSparks

Well-known Member
Joined
Mar 15, 2013
Messages
1,007
Office Version
  1. 2010
Platform
  1. Windows
best to declare all variables and not use Excel or VBA key words for variable names (ie: Table and Number)
try something like this
VBA Code:
    For j = 1 To ActiveSheet.ListObjects.Count
        Set tbl = ActiveSheet.ListObjects(j)
        With tbl
            For i = 1 To num
                .ListRows.Add AlwaysInsert:=True
            Next i
        End With
    Next j


 

McGuilliam

New Member
Joined
Oct 23, 2020
Messages
20
Office Version
  1. 2016
Platform
  1. Windows
best to declare all variables and not use Excel or VBA key words for variable names (ie: Table and Number)
try something like this
VBA Code:
    For j = 1 To ActiveSheet.ListObjects.Count
        Set tbl = ActiveSheet.ListObjects(j)
        With tbl
            For i = 1 To num
                .ListRows.Add AlwaysInsert:=True
            Next i
        End With
    Next j



Thanks for your input. Unfortunately this results in the same problem, the tables get updated in the wrong order. If fixable this would still be the best solution, but I did find another workaround by merging all tables. Less clean, but it does work.
 

Watch MrExcel Video

Forum statistics

Threads
1,114,323
Messages
5,547,247
Members
410,781
Latest member
fabalshehhi
Top