Excel VBA – Target a specific named table when multiple ListObjects exist on each worksheet

You’re on the right track already. The key change is that instead of referencing the first table on the worksheet (ListObjects(1)), you should explicitly reference the table by its name, since you already have a predictable naming convention (LotSize01 … LotSize12).

Using the month index, you can dynamically build the table name and safely target only the required table on each worksheet.

Below is a clean and robust version of your procedure.

Sub UpdateTables()

    Dim monthNames As Variant
    monthNames = Array("January", "February", "March", "April", "May", "June", _
                       "July", "August", "September", "October", "November", "December")

    Dim vals As Variant
    vals = Array("A", "B", "C", "D", "E")

    Dim ws As Worksheet
    Dim tbl As ListObject
    Dim lr As ListRow
    Dim i As Long, j As Long
    Dim tblName As String

    For i = LBound(monthNames) To UBound(monthNames)

        Set ws = ThisWorkbook.Worksheets(monthNames(i))
        tblName = "LotSize" & Format(i + 1, "00")

        On Error Resume Next
        Set tbl = ws.ListObjects(tblName)
        On Error GoTo 0

        If Not tbl Is Nothing Then
            With tbl
                ' Clear LotSize column
                If Not .ListColumns("LotSize").DataBodyRange Is Nothing Then
                    .ListColumns("LotSize").DataBodyRange.ClearContents
                End If

                ' Ensure table has exactly 5 rows
                Do While .ListRows.Count > 0
                    .ListRows(1).Delete
                Loop

                ' Populate SCRIP column
                For j = LBound(vals) To UBound(vals)
                    Set lr = .ListRows.Add
                    lr.Range(.ListColumns("SCRIP").Index).Value = vals(j)
                Next j
            End With
        End If

        Set tbl = Nothing
        Set ws = Nothing
    Next i

End Sub

The earlier suggestion from m_tarler to use ListObjects("LotSize"&Format(i,"00")) is absolutely correct — this version simply shows how to integrate that idea cleanly into your existing loop while fully meeting both of your requirements.

This approach should scale well even if additional tables are added later.

My answers are voluntary and without guarantee!

Hope this will help you.

To modify the VBA code to target the specific named table LotSize?? (where ?? is the two-digit month number) on each worksheet, even when multiple tables exist, follow these steps:

Key Changes:

Dynamic Table Name Construction:
For each month (e.g., January), construct the table name as LotSizeXX (e.g., LotSize01 for January).

Direct Table Reference:
Use ws.ListObjects("LotSizeXX") to directly access the target table instead of relying on the first table.

Error Handling:
Add On Error Resume Next to skip missing tables and avoid runtime errors.

Modified Code:

Sub UpdateTables()
    Dim monthNames As Variant
    monthNames = Array("January", "February", "March", "April", "May", "June", _
                        "July", "August", "September", "October", "November", "December")
    Dim vals As Variant
    vals = Array("A", "B", "C", "D", "E")
    Dim ws As Worksheet
    Dim tbl As ListObject
    Dim lr As ListRow
    Dim i As Long
    Dim j As Integer
    Dim monthNumber As Integer
    Dim targetTblName As String
    For i = LBound(monthNames) To UBound(monthNames)
        monthNumber = i + 1  ' January (index 0) → 01, February → 02, etc.
        targetTblName = "LotSize" & Format(monthNumber, "00")  ' e.g., "LotSize01"
        Set ws = ThisWorkbook.Worksheets(monthNames(i))    
        ' Directly reference the target table by name
        On Error Resume Next  ' Skip if table doesn't exist
        Set tbl = ws.ListObjects(targetTblName)
        On Error GoTo 0       ' Reset error handling
        If Not tbl Is Nothing Then
            With tbl
                ' Clear existing data in the table
                If Not .DataBodyRange Is Nothing Then
                    .DataBodyRange.ClearContents  ' Clears data but keeps structure
                End If               
                ' Add 5 rows and populate SCRIP column
                For j = 1 To 5
                    Set lr = .ListRows.Add(AlwaysInsert:=True)
                    lr.Range(.ListColumns("SCRIP").Index).Value = vals(j - 1)
                Next j
            End With
        Else
            Debug.Print "Table not found in sheet: " & ws.Name  ' Optional: Log missing tables
        End If
        Set tbl = Nothing  ' Release object reference
        Set ws = Nothing
    Next i
End Sub

The code handles 12 sheets automatically and can be extended by modifying monthNames.

This solution ensures the code operates on the correct table (LotSizeXX) regardless of other tables on the sheet.

My answers are voluntary and without guarantee!

Hope this will help you.

Was the answer useful? Mark as best response and like it!

This will help all forum participants.

To modify the VBA code to target the specific named table LotSize?? (where ?? is the two-digit month number) on each worksheet, even when multiple tables exist, follow these steps:

Key Changes:

 1. Dynamic Table Name Construction:
For each month (e.g., January), construct the table name as LotSizeXX (e.g., LotSize01 for January).

 2. Direct Table Reference:
Use ws.ListObjects("LotSizeXX") to directly access the target table instead of relying on the first table.

 3. Error Handling:
Add On Error Resume Next to skip missing tables and avoid runtime errors.

Modified Code:

Sub UpdateTables()
    Dim monthNames As Variant
    monthNames = Array("January", "February", "March", "April", "May", "June", _
                        "July", "August", "September", "October", "November", "December")
    Dim vals As Variant
    vals = Array("A", "B", "C", "D", "E")
    Dim ws As Worksheet
    Dim tbl As ListObject
    Dim lr As ListRow
    Dim i As Long
    Dim j As Integer
    Dim monthNumber As Integer
    Dim targetTblName As String
   
    For i = LBound(monthNames) To UBound(monthNames)
        monthNumber = i + 1  ' January (index 0) → 01, February → 02, etc.
        targetTblName = "LotSize" & Format(monthNumber, "00")  ' e.g., "LotSize01"
       
        Set ws = ThisWorkbook.Worksheets(monthNames(i))
       
        ' Directly reference the target table by name
        On Error Resume Next  ' Skip if table doesn't exist
        Set tbl = ws.ListObjects(targetTblName)
        On Error GoTo 0       ' Reset error handling      

        If Not tbl Is Nothing Then
            With tbl
                ' Clear existing data in the table
                If Not .DataBodyRange Is Nothing Then
                    .DataBodyRange.ClearContents  ' Clears data but keeps structure
                End If
               
                ' Add 5 rows and populate SCRIP column
                For j = 1 To 5
                    Set lr = .ListRows.Add(AlwaysInsert:=True)
                    lr.Range(.ListColumns("SCRIP").Index).Value = vals(j - 1)
                Next j
            End With
        Else
            Debug.Print "Table not found in sheet: " & ws.Name  ' Optional: Log missing tables
        End If
    
        Set tbl = Nothing  ' Release object reference
        Set ws = Nothing
    Next i
End Sub

The code handles 12 sheets automatically and can be extended by modifying monthNames.

This solution ensures the code operates on the correct table (LotSizeXX) regardless of other tables on the sheet.

My answers are voluntary and without guarantee!

Hope this will help you.

Was the answer useful? Mark as best response and like it!

This will help all forum participants.