VBA Loop through Sheets where sheetnames are made up of two variables

Question

Caveat: my VBA knowledge is self-taught via Googling so please be patient with me! I have a set of worksheets and am trying to perform the same set of actions through each sheet. Having problem with my codes: E.g. worksheets are "Staging AA 101", "Staging AA 102", "Staging AA 201", "Staging BB 101", "Staging BB 102", "Staging BB 201", "Staging CC 101", "Staging CC 102", "Staging CC 201"

My codes are:

Dim ExpSub(1 To 3) As String
Dim Post(1 To 3) As String
Dim i As Integer 'ExpSub
Dim j As Integer 'Post

ExpSub(1) = "101"
ExpSub(2) = "102"
ExpSub(3) = "201"
Post(1) = "AA"
Post(2) = "BB"
Post(3) = "CC"

For j = 1 To 3 'j for Post
For i = 1 To 3 'i for ExpSub
    Sheets("Staging " & Post(j) & " " & ExpSub(i)).Select
    Range("AK4").Select
    Range(Selection, Selection.Offset(100, 1)).Select 
    Selection.ClearContents
Next i
Next j

Errored out at this line Sheets("Staging " & Post(j) & " " & ExpSub(i)).Select

How do i loop through all the sheets with such variables please? Thanks in advance!

Answer 1

Your loops work as expected. You should write the result of your string concatenation into an intermediate variable and check with the debugger.

I can think of two reasons why your code fails:
a) One of your sheets is missing or has a typo in its name (maybe an extra space character). Check with the debugger for which sheet your code fails.

b) You don't specify the Workbook you want to work with, so VBA will look for the sheets in the Active Workbook - maybe this is a different workbook.

Beside this: You don't need to Select a sheet or a range to work with them, and it fact this should be avoided because it makes your code slow and is an open gate for bugs. You should take the time and read How to avoid using Select in Excel VBA.

Your code could look like

Dim ExpSub() As Variant, Post As Variant
ExpSub = Array("101", "102", "201")
Post = Array("AA", "BB", "CC")

Dim i As Integer 'ExpSub
Dim j As Integer 'Post
For j = LBound(Post) To UBound(Post) 'j for Post
    For i = LBound(ExpSub) To UBound(ExpSub)  'i for ExpSub
        Dim sheetname As String
        sheetname = "Staging " & Post(j) & " " & ExpSub(i)
        Debug.Print sheetname
        
        With ThisWorkbook.Sheets(sheetname)
            Dim r As Range
            Set r = .Range("A4").Resize(100, 1)
            r.ClearContents
        End With
    Next i
Next j

The code assumes that the sheets are in the same Workbook as your code (ThisWorkbook), else you need to change that.
If you like loooong statements, instead of the With-Block you can write a single line. Personally, I hate such long statements as they are hard to read and if they fail, you don't know which part caused the fail.

ThisWorkbook.Sheets(sheetname).Range("A4").Resize(100, 1).ClearContents

Answer 2

Clear Ranges

Option Explicit

Sub ClearRanges()
    
    Const wsNameLeft As String = "Staging "
    Const rgAddress As String = "AK4:AL104"
    Dim ExpSub As Variant: ExpSub = VBA.Array("101", "102", "201")
    Dim Post As Variant: Post = VBA.Array("AA", "BB", "CC")
    
    Dim wb As Workbook: Set wb = ThisWorkbook ' workbook containing this code
    
    Dim ws As Workbook
    Dim wsName As String
    Dim e As Long
    Dim p As Long
    
    For p = 0 To UBound(Post)
        For e = 0 To UBound(ExpSub)
            wsName = wsNameLeft & Post(p) & " " & ExpSub(e)
            On Error Resume Next
                Set ws = wb.Worksheets(wsName)
            On Error GoTo 0
            If Not ws Is Nothing Then ' worksheet found
                ws.Range(rgAddress).ClearContents
                Set ws = Nothing
            Else ' worksheet not found
                ' Look in the immediate window VBE CTRL+G
                Debug.Print "Worksheet '" & wsName & "' not found."
            End If
        Next e
    Next p
    
    'Range("AK4").Select
    
    MsgBox "Ranges cleared.", vbInformation

End Sub