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

'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

Example

Sheet1, 2 and 3 contain three tables.

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"

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.

Get the Excel file

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

---

Weekly Blog EMAIL

[newsletter_signup_form id=1]

Welcome! I am Oscar and here to help you out.

Feel free to comment and ask Excel questions.

Make sure you subscribe to my newsletter so you don't miss new blog articles.

Related articles

---

Count unique distinct values in a filtered Excel defined Table

This article demonstrates a formula that counts unique distinct values filtered from an Excel defined Table. Debra Dalgleish described in […]

How to highlight row of the selected cell programmatically

Today I would like to share with you these small event handler procedures that make it easier for you to […]

Populate a combo box (form control) [VBA]

In this tutorial I am going to explain how to: Create a combo box (form control) Filter unique values and […]

Filter values that exists in all three columns

This article explains how to extract values that exist in three different columns, they must occur in each of the […]

Basic data entry [VBA]

In this small tutorial, I am going to show you how to create basic data entry with a small amount […]

Prepare data for Pivot Table – How to split concatenated values?

This article demonstrates a macro that allows you to rearrange and distribute concatenated values across multiple rows in order to […]