Link to home
Start Free TrialLog in
Avatar of sir plus
sir plusFlag for Australia

asked on

Select an array of worksheets using vba

Hi
I have a workbook where I want to select a bunch of sheets at the same time.

I'd like to make the selectin using vba

Sub Comparesheets()
    Dim objSht As Worksheet
    Dim strShtArray As String
        For Each objSht In ThisWorkbook.Worksheets
            If objSht.Range("A166").Value = "11/6/2012" Then
                If strShtArray = vbNullString Then
                    strShtArray = objSht.Name
                Else
                    strShtArray = strShtArray & ", " & objSht.Name
                End If
                
            End If
        Next objSht
    Worksheets(Strings.Split(strShtArray)).Select
   ' Worksheets(Array(strShtArray)).Select
    Debug.Print strShtArray
End Sub

Open in new window



I get the array ok only cant select it.
For some easy points, how can I make this work?
SOLUTION
Avatar of sir plus
sir plus
Flag of Australia image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
SOLUTION
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
ASKER CERTIFIED SOLUTION
Avatar of Rory Archibald
Rory Archibald
Flag of United Kingdom of Great Britain and Northern Ireland image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of sir plus

ASKER

Hi
Thanks for all this
It will certainly be faster than extending the selection.
Dates are text (and not created by excel)....

@ rorya
I like yours as it fixes the specific error I was making (I swear I checked a watch in the actual project only I guess not) only I think creating the array right from the get go is a more eloquent solution.

We have 3 great solutions, and both answered the precise question (albeit after I posted my work around); are you both happy for me to split the points between you .....
Fine with me :)
Of course. :-)
No more needs to be said
All are great!