How do I build a Macro that can identify wooksheet names
 

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

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

"peter147" wrote in message
...
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

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


"Robin Hammond" wrote in message
...
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

"peter147" wrote in message
...
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

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" wrote in message
news:ZxEje.13791$Fv.8377@lakeread01...
With Dim vSheet as "Variant"
within the Function SheetExists can I use strName As "String"?


"Robin Hammond" wrote in message
...
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

"peter147" wrote in message
...
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

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

"Robin Hammond" wrote in message
...
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" wrote in message
news:ZxEje.13791$Fv.8377@lakeread01...
With Dim vSheet as "Variant"
within the Function SheetExists can I use strName As "String"?


"Robin Hammond" wrote in message
...
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

"peter147" wrote in message
...
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