The following macro inserts a new sheet to your workbook and lists all Excel defined Tables and corresponding Table headers in the active workbook.

1. Macro - VBA code

'Name macro
Sub ListTables()

'Declare variables and data types
Dim tbl As ListObject
Dim WS As Worksheet
Dim i As Single, j As Single

'Insert new worksheet and save to object WS
Set WS = Sheets.Add

'Save 1 to variable i
i = 1

'Go through each worksheet in the worksheets object collection
For Each WS In Worksheets

    'Go through all Excel defined Tables located in the current WS worksheet object
    For Each tbl In WS.ListObjects
        
        'Save Excel defined Table name to cell in column A
        Range("A1").Cells(i, 1).Value = tbl.Name

        'Iterate through columns in Excel defined Table
        For j = 1 To tbl.Range.Columns.Count

            'Save header name to cell next to table name
            Range("A1").Cells(i, j + 1).Value = tbl.Range.Cells(1, j)

        'Continue with next column
        Next j

        'Add 1 to variable i
        i = i + 1
    
    'Continue with next Excel defined Table
    Next tbl

'Continue with next worksheet
Next WS

'Exit macro
End Sub

Back to top

2. List Excel Tables - example

Sheet1, 2 and 3 contain three tables.

Back to top

3. Run macro

1. Go to "Developer" tab
2. Press with left mouse button on "Macros" button
3. Press with left mouse button on "ListTables"
4. Press with left mouse button on "Run"

Back to top

4. Where to copy macro

1. Press Alt+F11 to open the VB Editor
2. Press with left mouse button on "Insert" on the menu and then press with left mouse button on module.
3. Copy above VBA code and paste to code module, see image above.

Back to top

5. Get file

Get the Excel file

List-all-tables-and-headers-in-a-workbook.xlsm

Back to top