VBA code to call different macro depending on part of Worksheet name

Question

I am working on a macro that will cycle through all of the sheets in the active workbook and will then clear a certain part of a particular worksheet, based on whether one of the relevant keywords is contained in the worksheet name. In each case the worksheet name will be different, but any I want to clear will contain one of the key words below.

I have set up a separate macro to clear the range of cells in each case. If the Worksheet name does not contain any of the keywords, I want the macro to move onto the next worksheet.

My ultimate aim is to be able to apply this to numerous different workbooks, as the project I am working on is split by region, with a separate Excel file per region.

The code I have been trying is below. There are no errors appearing when I run the code, the code does not seem to run either, in fact nothing at all happens!

Any guidance or advice would be greatly appreciated.

Sub Loop_Customer_Sheets()

    Dim ws As Integer
    Dim i As Integer

    ws = ActiveWorkbook.Worksheets.Count

    For i = 1 To ws
        If ActiveSheet.Name Like "*ABC*" Then
            Call ABCInfoClear
        ElseIf ActiveSheet.Name Like "*DEF*" Then
            Call DEFInfoClear
        ElseIf ActiveSheet.Name Like "*GHI*" Then
            Call GHIInfoClear
        Else:
        End If
    Next i
End Sub

Answer 1

"Nothing at all happens" - fixing the issue with your code:

Your issue is that you are looping through the number of sheets, but you are only checking the ActiveSheet, which never changes! Replace your code with

ws = ActiveWorkbook.Worksheets.Count
For i = 1 To ws
    With ActiveWorkbook.WorkSheets(i)
        If .Name Like "*ABC*" Then
            ABCInfoClear
        ElseIf .Name Like "*DEF*" Then
            DEFInfoClear
        ElseIf ActiveSheet.Name Like "*GHI*" Then
            GHIInfoClear
        End If
    End With
Next i

Note: you don't need the Call keyword, you can just call subs as presented above.

---

Alternative solutions

A better option than having numerous macros might be to create a generic sub like

Sub ClearRangeInSheet(rangeAddress As String, sh As WorkSheet)
    Dim myRange As Range
    Set myRange = sh.Range(rangeAddress)
    myRange.ClearContents
    ' Any other cell clearing code e.g. for formatting here
End Sub 

Then call in the loop

Dim wsCount as Long
wsCount = ActiveWorkbook.WorkSheets.Count
For i = 1 to wsCount
    With ActiveWorkbook
        If .WorkSheets(i).Name Like "*ABC*" Then
            ' Always pass ".WorkSheets(i)", but change the range address as needed
            ClearRangeInSheet("A1:A20", .WorkSheets(i))
        ElseIf ' Other worksheet name conditions ...

        End If
    End With
Next I

---

As suggested in the comments, you could ditch indexing the sheets, and just loop through the sheet objects themselves:

Dim wksht as WorkSheet
For Each wksht In ActiveWorkbook.WorkSheets
    If wksht.Name Like "*ABC*" Then
        ' Always pass wksht but change the range address as needed
        ClearRangeInSheet("A1:A20", wksht)
    ElseIf ' Other worksheet name conditions ...

    End If
Next wksht