Copy multiple tables keeping filters

[JorgenKjer]

Hi<o></o>
Can anyone help me with an Excel VBA code, which can copy Tables from multiple worksheets to a master sheet?<o></o>
The Tables Can have different number of columns and rows but are all starting I cell A9 on each worksheet<o></o>
Before copying,the tables to the master sheet all data on the master sheet from A6 and down and over should be deleted. <o></o>
Before copyingthe tables all filters should be cleared / reset.<o></o>
When copyingthe table to the master sheet the formatting of the table should be kept including the filter headings for each table so the filter function still works.<o></o>
The first copied table is past to cell A6 in the master sheet and ends at for example in cellA30 the next copy should be past at cell A31.<o></o>
Over time there might be added more worksheets with tables to the workbook. This new worksheet should automatically be included when running the program.<o></o>
I hope this all makes sense and someone can help me<o></o>
With best regards<o></o>
Jorgen<o></o>

 

[igold]

Hi,

Are these really Tables or are they ranges of data? If they are Tables, then the Table names would be a big help.

 

[JorgenKjer]

Hi igold<o></o>
Thank youfor showing interest in my problem<o></o>
The names Tablenames are a mix of English and Danish but can bet change I necessary<o></o>
BaseFrameAssy<o></o>
BearingHouse<o></o>
CouplingHouse<o></o>
Gearbox<o></o>
Generator<o></o>
MainShaft<o></o>
NacelleForlængerBjælke<o></o>
NacelleLukkeBjælke<o></o>
NacelleTransportBjælke<o></o>
NacelleÅbenBjælke<o></o>
PCM_TransportBjælke<o></o>
TippingProtection<o></o>
TrolleyMSI_V164<o></o>
I hope thisis what you meant by table names<o></o>
As mentionedthere will over time be more worksheets each containing a table<o></o>
Sincerely<o></o>
Jorgen<o></o>

 

[igold]

Hi Jorgen,

Sorry for the delay.

See if this gets you close to what you are looking for. Given that you are going to be adding sheets and tables in the future, I thought the best way to handle that would be if you added another sheet to your workbook named "Lists". On the "Lists" worksheet, starting in Cell A2 (I left A1 for a header) you will list the names of the tables down Column A in the order that you want them copied to the "Master". Each time you add a new table you must add the name of the new table to the "Lists" worksheet. If you prefer, you do not have add another sheet, but the list of the table names must appear somewhere in your workbook, just be sure to change the reference in the code. Also, make sure that you have a worksheet named "Master".

I hope that makes sense.

Sub TableCopy()
    
    Dim wsM As Worksheet: Set wsM = Worksheets("Master")
    Dim wsL As Worksheet: Set wsL = Worksheets("Lists")
    Dim ws As Worksheet
    Dim tbl As ListObject
    Dim LstlRow As Long, i As Long, tblRowCt As Long
    Dim tColl As Variant
    
    Application.ScreenUpdating = False
    
    'Assign the list of table names to the tColl array
    wsL.Activate
    tColl = wsL.Range("A2:A" & Cells(Rows.Count, 1).End(xlUp).Row)
    
    'Clear the Master sheet
    wsM.Activate
    wsM.Range(Cells(6, 1), Cells(Rows.Count, Columns.Count)).Clear
    tblRowCt = 6
    
    'Loop through the sheets and match the table names and copy to Master
    For i = LBound(tColl) To UBound(tColl)
        For Each ws In ActiveWorkbook.Worksheets
            If ws.ListObjects.Count > 0 Then
                If ws.ListObjects(1).Name = tColl(i, 1) Then
                    Set tbl = ws.ListObjects(tColl(i, 1))
                    tbl.Range.Copy
                    wsM.Range("A" & tblRowCt).PasteSpecial (xlPasteAll)
                    tblRowCt = tblRowCt + tbl.ListRows.Count + 1
                    Application.CutCopyMode = False
                End If
            End If
        Next
    Next
    
    Range("A1").Select
    Application.ScreenUpdating = True


End Sub

I hope this helps.