How do I build a Macro that can identify wooksheet names

[Guest]

I am building a macro within excel, I have to switch between a number of
worksheets within a workbook. I need the same macro to run in a number of
similar workbooks; however some workbooks do not contain all the worksheets.
How can I use a formula to ignore the commands relating to the missing
worksheets?
Thank you for your help

Peter Davenport

 

[Robin Hammond]

Peter,

Try this:

Sub MainLoop
Dim vSheets as Variant
Dim vSheet as Variant
vSheets = Array("A","B","C")
For Each vSheet in vSheets
If SheetExists(vSheet) Then
'do something
End If
Next vSheet
End Sub

Public Function SheetExists(strName As String, Optional wbName As Workbook)
As Boolean
Dim shTest As Worksheet
If ActiveWorkbook Is Nothing And wbName Is Nothing Then Exit Function
If wbName Is Nothing Then Set wbName = ActiveWorkbook
On Error Resume Next
Set shTest = wbName.Sheets(strName)
On Error GoTo 0
SheetExists = Not shTest Is Nothing
End Function

Robin Hammond
www.enhanceddatasystems.com

 

[Jim May]

With Dim vSheet as "Variant"
within the Function SheetExists can I use strName As "String"?

 

[Robin Hammond]

Oops. The function's been tested. I just wrote the MainLoop on the fly in
the message to illustrate. It should have a type conversion to string in the
call to the function as follows.

Sub MainLoop()
Dim vSheets As Variant
Dim vSheet As Variant
vSheets = Array("A", "B", "C")
For Each vSheet In vSheets
If SheetExists(CStr(vSheet)) Then
'do something
End If
Next vSheet
End Sub

Public Function SheetExists(strName As String, Optional wbName As Workbook)
As Boolean
Dim shTest As Worksheet
If ActiveWorkbook Is Nothing And wbName Is Nothing Then Exit Function
If wbName Is Nothing Then Set wbName = ActiveWorkbook
On Error Resume Next
Set shTest = wbName.Sheets(strName)
On Error GoTo 0
SheetExists = Not shTest Is Nothing
End Function

Robin Hammond
www.enhanceddatasystems.com

 

[Jim May]

Thanks Robin;
I think I actually understand,,
WOW !!
Appreciate your help,
Jim