jeudi 22 juin 2017

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

I am working on a macro that will cycle through all of the sheets in an 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

Aucun commentaire:

Enregistrer un commentaire