VBA Selecting multiple sheets using a range

Question

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

But the only way I have found so far relates to using arrays and "hard-coding" the sheet names in, and would like instead to use Ranges

here is the code I have tried so far

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

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

Thanks

Answer 1

Try this:

Sub Macro1()
    Dim sheetArray() As String
    Dim i As Integer
    i = 0
    For Each c In Range("C2:C5").Cells
        ReDim Preserve sheetArray(0 To i)
        sheetArray(i) = c.Value
        i = i + 1
    Next
    Sheets(sheetArray).Select
End Sub

You may also consider adding verification if the sheet with that name exists before adding it to array.

Answer 2

3 lines of code needed (2, if you want ActiveSheet selected as well):

Sub sSheets()

Set xRange = Range("C2:C5") 'define ur range
  Sheets(xRange.Cells(1).Value).Select    'this is only needed to de-select the ActiveSheet
For Each xCell In xRange: Sheets(xCell.Value).Select False: Next  '[False] is like holding Ctrl and clicking on different tabs

End Sub

Answer 3

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