VBA copy row in tables down/specific order of adjustment

[McGuilliam]

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:

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.

 

[NoSparks]

best to declare all variables and not use Excel or VBA key words for variable names (ie: Table and Number)
try something like this

    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

The VBA Guide To ListObject Excel Tables

For a data analyst, Excel Tables are a necessity! They are the most efficient way to organize your raw data and refer to data that contracts or expands on a regular basis. Likewise, Excel tables can be extremely useful in combination with VBA.

www.thespreadsheetguru.com

 

[McGuilliam]

best to declare all variables and not use Excel or VBA key words for variable names (ie: Table and Number)
try something like this

    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

The VBA Guide To ListObject Excel Tables

For a data analyst, Excel Tables are a necessity! They are the most efficient way to organize your raw data and refer to data that contracts or expands on a regular basis. Likewise, Excel tables can be extremely useful in combination with VBA.

www.thespreadsheetguru.com

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.