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.