Trying to loop through worksheets and format specific tables in them

Question

Instead of coding for every specific worksheet to format specific tables, I want to create a loop that formats only the tables in the list, on different worksheets.

current code: (sh and wbTemplate defined and set earlier in the code)

Dim AllTableNames As String
Dim TableNames As Variant
Dim i As Long

    AllTableNames = "Table_Dormant_Stock,Table_Overstock,Table_Negative_Stock,Table_Outdated_Stock_Counts,Table_Waste_Returns"
    TableNames = Split(AllTableNames, ",")

    For Each sh In wbTemplate.Worksheets
        For i = LBound(TableNames) To UBound(TableNames)
           sh.ListObjects(TableNames(i)).DataBodyRange.Font.Size = 10
        Next i
    Next sh

I get "Run-time Error "9": Subscript out of range". I know it has something to do with the second loop, I've never done it before so not sure how to fix it.

Answer 1

Here's one way of fixing your code:

For Each sh In wbTemplate.Worksheets
    For i = LBound(TableNames) To UBound(TableNames)

        For Each tbl In sh.ListObjects
            If tbl.Name = TableNames(i) Then
                tbl.DataBodyRange.Font.Size = 10
            End If
        Next tbl

    Next i
Next sh

So I've used your code to cycle through each sheet and each tablename in your array - but then instead of running the code on that, we check every table (tbl) on sheet sh and if the name matches the name you're looking for, then the code runs.

---

If you want to refresh any and all tables on each worksheet, then this is simple:

For Each sh In wbTemplate.Worksheets
    For Each tbl In sh.ListObjects
        tbl.DataBodyRange.Font.Size = 10
    Next
Next sh