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

Move date out one month or year
Use =EDATE(A2,1) for one month later. Use EDATE(A2,12) for one year later.

NoSparks

Well-known Member
Joined
Mar 15, 2013
Messages
1,003
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,069
Messages
5,545,803
Members
410,708
Latest member
SanTrapGamer
Top