Consolidate formatted tables?

Question

I am trying to consolidate multiple formatted tables across all sheets of a workbook into one main table.

I have the following VBA to loop all the sheets and tables:

Sub GatherTables()

Dim tbl As ListObject
Dim sht As Worksheet

For Each sht In ThisWorkbook.Worksheets
    sht.Activate
    If sht.Name = "Summary" Or sht.Name = "Consolidated" Then
        'do nothing
    Else
        For Each tbl In sht.ListObjects
            Range(tbl.Name).Select
            Selection.Copy
            'do something with it
        Next tbl
    End If
Next sht

End Sub

When I run that code, at the line Range(tbl.Name).Select I get a runtime 1004 error.

I thought that my issue was I couldn't select a range without first selecting the sheet, hence the line "sht.Activate". Alas, no luck.

I then thought that maybe because tbl was a listobject, the type was wrong, so I tried using a temporary string variable and save the tbl.name to that first and use that in the range(tempvariable).select Alas, no luck.

I also tried just hard coding the name of the table: range("tablename").select, but that didnt work either...

I know its something simple but I am hung so can someone explain what I am missing?

Answer 1

I would normally refer to a ListObject table in the following way:

Sub GatherTables()

Dim tbl As ListObject
Dim sht As Worksheet

For Each sht In ThisWorkbook.Worksheets
    sht.Activate
    If sht.Name = "Summary" Or sht.Name = "Consolidated" Then
        'do nothing
    Else
        For Each tbl In sht.ListObjects
            ActiveSheet.ListObjects(tbl.Name).Range.Select
            Selection.Copy
            'do something with it
        Next tbl
    End If
Next sht

End Sub