In VBA, how do I dynamically assign sheets to a sheets collection-CodePudding

The following code works:

    Dim shts As Sheets
    Set shts = Sheets(Array("Sheet1", "Sheet2"))

What I would like to do is add sheets that may be created in the future to the shts collection. The way I thought this would be accomplished involves using a loop where the sheet names are joined in a large string, making sure to obey the same formatting as in the example above. This is my non-working code:

    Dim shts As Sheets
    Dim wks() As Worksheet
    Dim str As String
    
    ReDim wks(0 To Sheets.Count)
    Set wks(0) = Sheets(1)
    
    str = wks(0).Name & """"
    
    For i = 1 To UBound(wks)
        Set wks(i) = Sheets(i)
        str = str & ", """ & wks(i).Name & ""
    Next i
        
    Set shtsToProtect = Sheets(Array(str)) ' ERROR
    [Run-time error '9': Subscript out of range]

I've tried several variants of the string argument, still no luck.

CodePudding user response：

You can collect the subset sheets name into a string separated by given delimiter and then use Split() function to get an array out of it

Sub Test()

    With ThisWorkbook
    
        Dim shSubSetNames As String
        Dim sh As Worksheet
        For Each sh In .Worksheets
        
            If sh.Name Like "Sheet*" Then ' change criteria as per your needs
            
                shSubSetNames = shSubSetNames & sh.Name & "|"
                
            End If
        
        Next
        
            If shSubSetNames <> vbNullString Then
            
                shSubSetNames = Left$(shSubSetNames, Len(shSubSetNames) - 1)
        
                    Dim subSetShts As Sheets
                        Set shts = Sheets(Split(shSubSetNames, "|"))
                    
                        shts.Select
        
            End If
    
    End With    
    
End Sub

CodePudding user response：

This is a solution to directly get to your goal, based on the code you wrote so far. You probably need to realize that having an array of worksheet names is not the same as only a string of names separated by commas. The latter is still just a string, not an array.

Sub Foo()

Dim i As Long
Dim shts() As String

ReDim shts(1 To ThisWorkbook.Worksheets.Count)
For i = 1 To ThisWorkbook.Worksheets.Count
    shts(i) = ThisWorkbook.Worksheets(i).Name
Next i

Worksheets(shts).Select

End Sub

But as noted in the comments, there is something much simpler you can do:

Sub Bar()

    ActiveWorkbook.Worksheets.Select

End Sub