[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

Macro to select multiple worksheets

Posted on 2011-10-29
4
Medium Priority
?
307 Views
Last Modified: 2012-05-12
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.
0
Comment
Question by:KP_SoCal
  • 2
  • 2
4 Comments
 
LVL 93

Accepted Solution

by:
Patrick Matthews earned 2000 total points
ID: 37050871
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
 

Author Closing Comment

by:KP_SoCal
ID: 37051058
Patrick, this is a great solution.  Thanks so much!
0
 
LVL 93

Expert Comment

by:Patrick Matthews
ID: 37051064
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
 

Author Comment

by:KP_SoCal
ID: 37054167
Excellent again!  Thanks as always!! ;-)
0

Featured Post

Get your Disaster Recovery as a Service basics

Disaster Recovery as a Service is one go-to solution that revolutionizes DR planning. Implementing DRaaS could be an efficient process, easily accessible to non-DR experts. Learn about monitoring, testing, executing failovers and failbacks to ensure a "healthy" DR environment.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Lost Word File? Eagerly, need it back? Read ahead; this File Recovery guide is for you.
In this post, I will showcase the steps for how to create groups in Office 365. Office 365 groups allow for ease of flexibility and collaboration between staff members.
This Micro Tutorial will demonstrate how to use longer labels with horizontal bar charts instead of the vertical column chart.
Look below the covers at a subform control , and the form that is inside it. Explore properties and see how easy it is to aggregate, get statistics, and synchronize results for your data. A Microsoft Access subform is used to show relevant calcul…

872 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question