Sheets Array Selection

[JJ]

I would like to select all sheets specified in a cell range. The sheets
needed to be selected change on differenet criteria, so I have used a formula
to concatenate the sheet names that need to be selected. In the VBA I defined
that cell as a string and then used the sheets(array(x)).select.

Here is an example:
Workbook contains 3 sheets named: "Test 1", "Test 2", and "Test 3"

The value in "Test 1" Range A1 = "Test 2", "Test 3"


Sub SelectSheetsMacro ()

Sheets("Test 1").Select

Dim x as string
x = Range("A1")

Sheets(Array(x)).Select

End Sub

I continue to get an error message....please help..

[Tom Ogilvy]

You can't do it that way

Sub SelectSheets()
Dim i As Long
Dim v() As Variant
ReDim v(0 To 0)
i = 1
With Worksheets("Sheet1")
Do While Not IsEmpty(.Cells(i, 1))
ReDim Preserve v(0 To i - 1)
v(i - 1) = .Cells(i, 1).Value
i = i + 1
Loop
End With
Worksheets(v).Select
End Sub

worked for me. Assumes you will have at least two sheet names and that the
first sheet name is in cell A1 of sheet1 with the remainder following
immediately below it in column A.
--
Regards,
Tom Ogilvy



"JJ" wrote:

> I would like to select all sheets specified in a cell range. The sheets
> needed to be selected change on differenet criteria, so I have used a formula
> to concatenate the sheet names that need to be selected. In the VBA I defined
> that cell as a string and then used the sheets(array(x)).select.
>
> Here is an example:
> Workbook contains 3 sheets named: "Test 1", "Test 2", and "Test 3"
>
> The value in "Test 1" Range A1 = "Test 2", "Test 3"
>
>
> Sub SelectSheetsMacro ()
>
> Sheets("Test 1").Select
>
> Dim x as string
> x = Range("A1")
>
> Sheets(Array(x)).Select
>
> End Sub
>
> I continue to get an error message....please help..
>
>

[Tom Ogilvy]

You can't do it that way

Sub SelectSheets()
Dim i As Long
Dim v() As Variant
ReDim v(0 To 0)
i = 1
With Worksheets("Sheet1")
Do While Not IsEmpty(.Cells(i, 1))
ReDim Preserve v(0 To i - 1)
v(i - 1) = .Cells(i, 1).Value
i = i + 1
Loop
End With
Worksheets(v).Select
End Sub

worked for me. Assumes you will have at least two sheet names and that the
first sheet name is in cell A1 of sheet1 with the remainder following
immediately below it in column A.
--
Regards,
Tom Ogilvy



"JJ" wrote:

> I would like to select all sheets specified in a cell range. The sheets
> needed to be selected change on differenet criteria, so I have used a formula
> to concatenate the sheet names that need to be selected. In the VBA I defined
> that cell as a string and then used the sheets(array(x)).select.
>
> Here is an example:
> Workbook contains 3 sheets named: "Test 1", "Test 2", and "Test 3"
>
> The value in "Test 1" Range A1 = "Test 2", "Test 3"
>
>
> Sub SelectSheetsMacro ()
>
> Sheets("Test 1").Select
>
> Dim x as string
> x = Range("A1")
>
> Sheets(Array(x)).Select
>
> End Sub
>
> I continue to get an error message....please help..
>
>