vba - Selecting multiple sheets using a range

Question

I have sheet names in cells C2 to C5, which are dynamic. I would like to select them at the same time using VBA.

The only way I have found uses arrays and "hard-coding" the sheet names.

Sub ssheets()
    Worksheets(Array("Sheet2", "Sheet3","Sheet4","Sheet5")).Select
End Sub

I would like something that uses Range("C2:C5") so that I can select the relevant sheets without having to type in "Sheet2", "Sheet3","Sheet4","Sheet5" etc.

Answer 1

The sheet names array has to be of type Variant containing a one dimensional array. The Range("C2:C5") returns a two dimensional array. To use this as sheet names array, you have to transpose it.

Sub ssheets()
    Dim oWS As Worksheet
    Dim aSheetnames As Variant
    Set oWS = Worksheets(1)
    aSheetnames = oWS.Range("C2:C5")
    aSheetnames = Application.WorksheetFunction.Transpose(aSheetnames)
    Worksheets(aSheetnames).Select
End Sub