Home |
Search |
Today's Posts |
#1
|
|||
|
|||
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 |
#2
|
|||
|
|||
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 |
#3
|
|||
|
|||
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 |
#4
|
|||
|
|||
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 |
#5
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Issuing macro in workbook from separate workbook | Excel Discussion (Misc queries) | |||
Can I build 'goal seek' facility into a macro? | Excel Discussion (Misc queries) | |||
Playing a macro from another workbook | Excel Discussion (Misc queries) | |||
Date macro | Excel Discussion (Misc queries) | |||
Macro and If Statement | Excel Discussion (Misc queries) |