This macro creates a new sheet and lists all tables and corresponding table headers in a workbook.

Sub ListTables()
Dim tbl As ListObject
Dim WS As Worksheet
Dim i, j As Single
Set WS = Sheets.Add
i = 1
For Each WS In Worksheets
    For Each tbl In WS.ListObjects
        Range("A1").Cells(i, 1).Value = tbl.Name
        For j = 1 To tbl.Range.Columns.Count
            Range("A1").Cells(i, j + 1).Value = tbl.Range.Cells(1, j)
        Next j
        i = i + 1
    Next tbl
Next WS
End Sub

Example

Sheet1, 2 and 3 contain three tables.

Run macro

  1. Go to "Developer" tab
  2. Click "Macros" button
  3. Click "ListTables"
  4. Click "Run"

Create macro

Open VB Editor, shortcut keys: Alt + F11.

Download excel 2007 macroenabled *.xlsm file

List all tables and headers in a workbook.xlsm

Related posts:

Quickly create links to sheets, tables, pivot tables and named ranges in a workbook

Search two related tables simultaneously (vba)

Create links to all sheets in a workbook

Match a criterion and extract multiple corresponding table headers

Excel:Print headers on every page