Trying to find all the data tables in my workbook

[jamie_in_nj]

Hi, I am using Excel 2010. I am digging through a workbook with 80-some worksheets. There is one worksheet with 11 data tables. When the workbook refreshes, there is a note at the bottom saying something about 21 data tables. I am not sure where the other 10 are. In an online post, someone said that the Name Manager should show them. But in Name Manager, when I filter on Tables, the list goes blank, and all the icons are the same for the other named ranges. Is there another way to find the data tables? Thanks.

 

[Legacy 346338]

Thanks to all who posted on this thread. I searched for "TABLE(" and found about a dozen TABLE formulas which somehow did not get moved when the rest of a data table was moved. This cleared up an annoying problem for one of my clients.

 

[gavcol]

You could try using this code to list the names and adddresses of each Table in your Workbook.

Sub List_Tables_In_Workbook()
    Dim wsSummary As Worksheet
    Dim tbl As ListObject
    Dim n As Long, lRow As Long

  
    '--add summary sheet with headers
    Set wsSummary = Worksheets.Add(Before:=Sheets(1))
    Range("A1:C1") = Array("Table Name", _
        "Found on Sheet", "at Range Address")
    lRow = 1 'Header Row

  
    '--step through each sheet and list each table
    For n = 2 To Worksheets.Count
        For Each tbl In Worksheets(n).ListObjects
            lRow = lRow + 1
            With wsSummary
                .Cells(lRow, "A") = tbl.Name
                .Cells(lRow, "B") = tbl.Parent.Name
                .Cells(lRow, "C") = tbl.Range.Address
            End With
        Next tbl
    Next n

  
End Sub

Hi Jerry, Thank you. 5-6 yrs later this bit of code has come in vry handy for me.
Can I pls trouble yo to include the table number in column D ?

The reason I ask is I've had a corruption in an excel file and after a seemingly successful repair I was given an xml report which referenced the following excerpt:
<repairedRecord>Repaired Records: External data range from /xl/queryTables/queryTable17.bin part (External data range)</repairedRecord>

The trouble I have is that I've renamed all of my tables to something more descriptive and have lost visibility of which one is queryTable17.
Hope you can help and Thans muchly in advance.

Cheers
Gavin