Macro to select multiple worksheets

Below is a macro that selects the following sheets: Main, Index, ResultA1, ResultBB, and Result92.

Sheets(Array("Main", "Index", "ResultA1", "ResultBB", "Result92", "Sheet8")).Select
Sheets("Main").Activate

Open in new window


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.
KP_SoCalAsked:
Who is Participating?
 
Patrick MatthewsConnect With a Mentor Commented:
The following appears to work for me.  Note how I forced the worksheet name to lower case before comparing; by default, Excel VBA uses case sensitive text comparisons.


Sub test()
    
    Dim arr() As String
    Dim ws As Worksheet
    
    ReDim arr(1 To 2) As String
    arr(1) = "Main"
    arr(2) = "Index"
    
    With ThisWorkbook
        For Each ws In .Worksheets
            If LCase(ws.Name) Like "result*" Then
                ReDim Preserve arr(1 To UBound(arr) + 1) As String
                arr(UBound(arr)) = ws.Name
            End If
        Next
    
        .Worksheets(arr).Select
        .Worksheets("Main").Activate
    End With
    
End Sub

Open in new window

0
 
KP_SoCalAuthor Commented:
Patrick, this is a great solution.  Thanks so much!
0
 
Patrick MatthewsCommented:
Glad to help :)

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

Open in new window

0
 
KP_SoCalAuthor Commented:
Excellent again!  Thanks as always!! ;-)
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.