VBA Loop Through Excel Tables by Name in a Workbook

In Excel VBA, access a table by its name through the worksheet’s ListObjects collection: use ThisWorkbook.Worksheets("Data").ListObjects("SalesTable"). To loop through tables, use a For Each loop over a worksheet’s ListObjects; to find a named table anywhere in a workbook, check each worksheet in turn.

Save a copy of the workbook before running any macro: VBA changes cannot be undone with Ctrl+Z. The examples below are for Excel for Microsoft 365 on Windows and use ordinary Excel table objects, also called ListObjects.

What VBA means by an Excel table

An Excel table is a structured range created with Insert > Table or Home > Format as Table. VBA represents each one as a ListObject. The collection of tables on a particular worksheet is Worksheet.ListObjects, and a table’s name is its Name property. For example, a table named SalesTable on a sheet named Data can be addressed as Worksheets("Data").ListObjects("SalesTable").

The worksheet and table names identify different things. A worksheet can contain several tables, and a table name is not the name of the sheet that holds it. In Excel, table names must be unique within the workbook, so a name can identify one table even when you do not yet know which sheet contains it. However, the ListObjects collection is attached to a worksheet, so direct lookup still needs a worksheet reference.

Access one table when you know its name

If you know both the worksheet and table names, assign the table to a ListObject variable. You can then use its properties or loop through its data rows. This example prints each product value from a table whose column header is Product:

Sub ReadNamedTable()
    Dim ws As Worksheet
    Dim tbl As ListObject
    Dim rowItem As ListRow
    Dim productColumn As Long

    ' Point to the sheet and table by their names.
    Set ws = ThisWorkbook.Worksheets("Data")
    Set tbl = ws.ListObjects("SalesTable")
    productColumn = tbl.ListColumns("Product").Index

    ' Read the Product value from each data row.
    For Each rowItem In tbl.ListRows
        Debug.Print rowItem.Range.Cells(1, productColumn).Value
    Next rowItem
End Sub

Here, ThisWorkbook means the workbook containing the VBA code. If the macro is stored in a separate personal macro workbook or add-in and should work on the workbook currently selected in Excel, use an explicitly assigned workbook variable or ActiveWorkbook instead. Do not substitute ActiveSheet casually: the active sheet can change, while a named worksheet reference makes the target clear.

Loop through every table on a worksheet

When the table names are unknown or you want to process all tables on one sheet, loop through that sheet’s ListObjects collection. The following macro writes each table’s name and number of data rows to the Immediate window in the VBA editor (open it with Ctrl+G on Windows):

Sub ListTablesOnDataSheet()
    Dim ws As Worksheet
    Dim tbl As ListObject

    ' Select the worksheet whose tables should be listed.
    Set ws = ThisWorkbook.Worksheets("Data")

    For Each tbl In ws.ListObjects
        Debug.Print tbl.Name, tbl.ListRows.Count
    Next tbl
End Sub

A sheet with no tables simply has an empty ListObjects collection, so the loop runs zero times. This is useful when a sheet’s layout can vary and you do not want to assume that at least one table exists. ListRows.Count counts data rows, not the header row or an optional totals row.

Find a table by name anywhere in the workbook

To search the whole workbook, loop over its worksheets and then over each worksheet’s tables. Comparing each table’s Name avoids trying to look up a table on a sheet that may not contain it. This example reports whether the requested table was found:

Sub FindTableInWorkbook()
    Dim ws As Worksheet
    Dim tbl As ListObject
    Dim foundTable As ListObject
    Dim targetName As String

    targetName = "SalesTable"

    ' Search every worksheet for the named table.
    For Each ws In ThisWorkbook.Worksheets
        For Each tbl In ws.ListObjects
            If StrComp(tbl.Name, targetName, vbTextCompare) = 0 Then
                Set foundTable = tbl
                Exit For
            End If
        Next tbl

        If Not foundTable Is Nothing Then Exit For
    Next ws

    If foundTable Is Nothing Then
        MsgBox "Table not found: " & targetName
    Else
        Debug.Print foundTable.Parent.Name, foundTable.Name
    End If
End Sub

foundTable.Parent refers to the worksheet that contains the table. The comparison uses StrComp with vbTextCompare, so the search does not depend on letter case. If you need to use the result later in the same macro, keep the foundTable variable and work with it after the search. If it remains Nothing, no matching table was found; check spelling and whether the table exists in the workbook being searched.

Loop through table rows and columns

Once you have the right table, use ListRows to process records and ListColumns to refer to fields by their visible header names. Referring to a column by name is generally easier to maintain than hard-coding its position: if someone moves the column, the name-based reference still points to the intended field.

For example, to inspect each row’s Status value, first get the column index from the table, then use that index inside each row’s range. The first argument to Cells is 1 because each rowItem.Range contains one table row; the second argument is the position of the Status column within the table.

For a single column’s full range, tbl.ListColumns("Status").DataBodyRange gives the data cells without the header. For the header, use tbl.HeaderRowRange; for an optional totals row, use tbl.TotalsRowRange. These properties help avoid accidentally processing headers or totals as if they were records.

Handle empty tables and missing names

A table can exist while containing no data rows. In that case, tbl.ListRows.Count is zero, and tbl.DataBodyRange is Nothing. The row loop shown earlier safely runs zero times, but code that tries to use DataBodyRange.Cells directly should check for Nothing first. Otherwise, an empty table can trigger a run-time error.

Direct lookup such as ws.ListObjects("SalesTable") is concise, but it raises an error if the name is absent from that worksheet. If a missing table is an expected possibility, search the collection and check whether a matching object was found, as in the workbook-wide example. That makes the outcome explicit rather than allowing an unhandled error to stop the macro.

Also check that the text is the table’s actual name, not its sheet name, a cell-range address, or a column heading. In Excel, click inside the table and inspect Table Design > Table Name to confirm it. Table names cannot contain spaces, which is another reason to copy the exact name shown there.

Common scope and reference mistakes

Using the wrong workbook: ThisWorkbook is where the code is stored; ActiveWorkbook is the workbook currently active in Excel. They can differ.
Looking on the wrong worksheet: ws.ListObjects("SalesTable") searches only that sheet. To search other sheets, use the nested workbook loop.
Confusing table names with range names: A ListObject is addressed through ListObjects, not through the worksheet’s Range collection.
Assuming a header is a data row: Use ListRows or DataBodyRange when processing records, rather than the table’s entire range.
Using an undeclared or incompatible loop variable: Declare worksheet and table variables as Worksheet and ListObject. With Option Explicit enabled, VBA will flag undeclared variables before the macro runs.

When a macro stops with an error, inspect the highlighted line and verify the workbook, sheet, and table names in the object reference. A spelling error in a direct collection lookup is a frequent cause; an empty table is a separate issue that matters when reading its data range.

Excel tables versus Access database tables

This answer uses “table” in the Excel sense: a formatted worksheet range represented by a ListObject. Access database tables are different objects. Access VBA commonly works with database table definitions through DAO’s TableDefs collection or retrieves records through a DAO recordset; Excel’s Worksheet.ListObjects collection does not enumerate Access tables. If the goal is to read data from an Access database in Excel, the appropriate method depends on whether you are using a connection, a query, or an installed DAO/ADO reference.

Frequently Asked Questions

How do I loop through all Excel tables in a workbook?
Loop through ThisWorkbook.Worksheets, then loop through each worksheet’s ListObjects collection. Use a Worksheet variable for the outer loop and a ListObject variable for the inner loop.

Can I access an Excel table by name without knowing its sheet?
Not with a single worksheet-level ListObjects lookup. Search each worksheet’s collection and compare each table’s Name, or store the table’s worksheet name as well as its table name.

What is the difference between ListObjects and ListRows?
ListObjects is a worksheet collection containing its tables. ListRows is a collection of the data rows inside one table; it excludes the header and totals row.

Why does VBA say the table name was not found?
The macro may be checking the wrong workbook or worksheet, or the name may be misspelled. Confirm the name under Table Design > Table Name and remember that a direct lookup searches only the worksheet named in the expression.

How do I loop through a table with no data rows?
A For Each loop over tbl.ListRows naturally performs no iterations when there are no data rows. If using tbl.DataBodyRange directly, test whether it is Nothing before accessing cells.

Does the same code work in Excel for Mac?
The Excel object model used here is available in Excel for Mac, but the VBA editor and some keyboard shortcuts differ from Windows. The Windows Immediate-window shortcut Ctrl+G may not match the Mac setup; use the Visual Basic Editor’s menus or the shortcut shown for your Mac version. The table and ListObject references themselves remain the same.

Try it: the Column Letter to Number: aA is 27, XFD is 16,384. Converts both ways.

More in VBA & macros

Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources

Spotted an error in this guide? Let us know.