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

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.