Macro to identify if work sheets exist then run seperate macros if it does and contine if not

[paul_sykes00]

Hi,

I am looking for a macro that will do the follow:
1) Check if "sheet x" exists
2) If does run an edit macro I have create (i.e. macro 1)
3) Then check if "sheet y" exists
4) If does run a 2nd edit macro I have create (i.e. macro 2)
5) End

Hope that makes sense. Look forward to your responses,

Thanks

Paul

 

[Michael M]

Try

Sub tester()
Dim ws As Worksheet
    For Each ws In Worksheets
        If ws.Name = "sheet x" Then Call macro1
        If ws.Name = "sheet y" Then Call macro2
Next ws
End Sub

 

[paul_sykes00]

Thanks Michael,

If the workbook has say 100 sheets is there a way to make the macro check all sheets to see if "sheet x" or/and "sheet y" exist?

 

[Michael M]

The posted code does that !!
Have you tried it ?
It looks at each sheet name and if it's neither name then goes to the next sheet until it either finds the named sheet or finds nothing.
Doesn't matter how many sheets there are.

 

[Jerry Sullivan]

Hi all,

Another approach is to employ a function. This avoids stepping through each sheet.

Sub tester2()
    If SheetExists("sheet x") Then Call Macro1
    If SheetExists("sheet y") Then Call Macro2
End Sub

Function SheetExists(sName As String) As Boolean
    On Error Resume Next
    SheetExists = Sheets(sName).Index > 0
End Function

 

[paul_sykes00]

Thanks guys, both methods worked well. The reason i was having difficulties at first was because I forgot to make the sheet I wanted to edit active at the start of Macro1,Macro2 etc

 

[Michael M]

OK, glad you got it sorted....