How To List All Excel Table Names

When you use tables in Excel worksheets, you always need to know the table names in those worksheets. This article will tell you how to list all Excel table names in your Excel workbook both manually and using VBA.

1. How To List All Excel Table Names Manually.

1. On the Excel worksheet top-left corner, click the down arrow of the Name Box input text box.
2. Then it will list all the table names in all the Excel worksheets.
3. If you can not find the table name in the list, you can type the table name in the Name Box and press the enter key to go to the table.
4. Another way to list all Excel table names is to enter =T in the Excel Formula Bar input text box, this will list all the Excel tables which names start with the character T.
5. You can use your own table name characters to list the related Excel tables, for example, =MyTable will list all the tables with the name beginning with MyTable.

2. How To List All Excel Table Names Using VBA.

1. You can also use VBA to list all Excel table names programmatically.
2. First, we should loop in the VBA Worksheets object to get each worksheet object.
3. Then we should loop in the worksheet object’s ListObjects.
4. Then we can get the table name with each list object’s Name property.
5. Below is an example VBA source code, in the below source code, it will first create a new Excel worksheet.
6. Then it will get all the tables’ names using VBA code, and add the table names to the Excel worksheet.

   Sub List_Excel_Tables()
   
       ' Define worksheet object variable.
       Dim sheetObject As Worksheet
       ' Define table object variable.
       Dim tableObject As ListObject
       
       ' Define an Integer type variable.
       Dim i As Integer
       
       ' Initialize the integer variable to -1.
       i = -1
       
       ' Add a worksheet that contains all the table names.
       ' Set the worksheet name text.
       Sheets.Add.Name = "List All Excel Table Names"
       
       ' Loop all the worksheets object in the current workbook.
       For Each sheetObject In Worksheets
       
           ' Loop all the tables in the selected worksheet.
           For Each tableObject In sheetObject.ListObjects
               
               ' Add 1 to the counter number.
               i = i + 1
               
               ' Apply the table object name to the worksheet cells below cell A1 one by one(A1, A2, A3......).
               Sheets("List All Excel Table Names").Range("A1").Offset(i).Value = tableObject.Name
               
           Next
       Next
   End Sub

References

1. How To Enable Developer Tab And Write VBA Source Code In Excel