KP_SoCal
asked on
Macro to select multiple worksheets
Below is a macro that selects the following sheets: Main, Index, ResultA1, ResultBB, and Result92.
I need this macro to be a bit more dynamic. Suppose I were to add a new result worksheet names 'Result99'. I would need to manually modify the code above.
Instead, I need a macro that selects 'Main', 'Index', and all sheet names that start with 'Result'. Any suggestions would be greatly appreciated.
Sheets(Array("Main", "Index", "ResultA1", "ResultBB", "Result92", "Sheet8")).Select
Sheets("Main").Activate
I need this macro to be a bit more dynamic. Suppose I were to add a new result worksheet names 'Result99'. I would need to manually modify the code above.
Instead, I need a macro that selects 'Main', 'Index', and all sheet names that start with 'Result'. Any suggestions would be greatly appreciated.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Glad to help :)
This one avoids ReDim altogether:
This one avoids ReDim altogether:
Sub test()
Dim MyList As String
Dim arr As Variant
Dim ws As Worksheet
MyList = "Main,Index"
With ThisWorkbook
For Each ws In .Worksheets
If LCase(ws.Name) Like "result*" Then
MyList = MyList & "," & ws.Name
End If
Next
arr = Split(MyList, ",")
.Worksheets(arr).Select
.Worksheets("Main").Activate
End With
End Sub
ASKER
Excellent again! Thanks as always!! ;-)
ASKER