Syntax problem - select many sheets at once

[Norman Goldsmith]

I know that the statement: Sheets(Array("Name1","Name2", "NameN")).Select
will select all of the sheets that are explicitly listed within Array(). For
my purposes, the number of sheets will always be different and their names
will always be different. I want to be able to select all but the first
sheet.

I created an Array I called SheetArray() with the names (or sheet numbers if
needed) of the sheets that I want to select. The variant SheetArray() was
ReDimed (that's a verb?) to hold the names.

What I can't figure out is the syntax that allows me to use the populated
SheetArray() within the Select statement. The simplistic
Sheets(SheetArray()).Select returns error 9, subscript out of range.

------------------------------------------------------
Code fragment:

Dim SheetKtr As Long
Dim SheetArray() As Variant

SheetKtr = ActiveWorkbook.Sheets.Count

ReDim SheetArray(SheetKtr - 1) '1 less than total number of
sheets
For I = 0 To SheetKtr - 2 'assuming zero based
array
SheetArray(I) = Sheets(I + 2).Name 'name of sheet #2 and beyond
Next I

Sheets(Array(?)).Select

Please help.

Excel 2003, SP3.

Norm

 

[Tim Zych]

Part of the problem is the array was dimensioned with one-too-many extra
elements.

Dim SheetKtr As Long, I As Long
Dim SheetArray() As Variant
SheetKtr = ActiveWorkbook.Sheets.Count
'1 less than total number of sheets (-1 for the zero-based arr)
ReDim SheetArray(0 To SheetKtr - 2)
For I = 0 To SheetKtr - 2
SheetArray(I) = Sheets(I + 2).Name
Next I
Sheets(SheetArray).Select

 

[Jim Cone]

Sub AtoZ()
Dim SheetKtr As Long
Dim SheetArray() As String
Dim I As Long

SheetKtr = ActiveWorkbook.Sheets.Count
ReDim SheetArray(2 To SheetKtr)

For I = 2 To SheetKtr
'name of sheet #2 and beyond
SheetArray(I) = Sheets(I).Name
Next I
Sheets(SheetArray).Select
End Sub
--
Jim Cone
Portland, Oregon USA


"Norman Goldsmith" <[email protected]>
wrote in message
I know that the statement: Sheets(Array("Name1","Name2", "NameN")).Select
will select all of the sheets that are explicitly listed within Array(). For
my purposes, the number of sheets will always be different and their names
will always be different. I want to be able to select all but the first
sheet.

I created an Array I called SheetArray() with the names (or sheet numbers if
needed) of the sheets that I want to select. The variant SheetArray() was
ReDimed (that's a verb?) to hold the names.

What I can't figure out is the syntax that allows me to use the populated
SheetArray() within the Select statement. The simplistic
Sheets(SheetArray()).Select returns error 9, subscript out of range.

------------------------------------------------------
Code fragment:

Dim SheetKtr As Long
Dim SheetArray() As Variant

SheetKtr = ActiveWorkbook.Sheets.Count

ReDim SheetArray(SheetKtr - 1) '1 less than total number of
sheets
For I = 0 To SheetKtr - 2 'assuming zero based
array
SheetArray(I) = Sheets(I + 2).Name 'name of sheet #2 and beyond
Next I

Sheets(Array(?)).Select

Please help.

Excel 2003, SP3.

Norm

 

[Ron de Bruin]

Test this one (no error check)

Sub test()
Dim I As Long
Dim S As Long
Dim ShArr() As String
For I = 2 To ActiveWorkbook.Sheets.Count
If Sheets(I).Visible = -1 Then
S = S + 1
ReDim Preserve ShArr(1 To S)
ShArr(S) = Sheets(I).Name
End If
Next I

If S > 0 Then Sheets(ShArr).Select
End Sub

 

[Jim Cone]

-or-
'--
Sub ZtoA()
Dim I As Long
Sheets(2).Select
For I = 3 To Sheets.Count
Sheets(I).Select False
Next
End Sub

 

[Mike H]

Hi,

I'm not really sure what your tring to do so this code now reads you sheet
names into an array and selects one at the end

Sheets(SheetArray(0)).Select

or to demonstrate it has them all gives their names

For x = LBound(SheetArray) To UBound(SheetArray)
MsgBox Sheets(SheetArray(x)).Name
Next



Sub Cell_Down()
Dim SheetKtr As Long
Dim SheetArray As Variant
SheetKtr = ActiveWorkbook.Sheets.Count
ReDim SheetArray(SheetKtr - 1)

For I = 0 To SheetKtr - 1
SheetArray(I) = Sheets(I + 1).Name
Next I
Sheets(SheetArray(0)).Select


For x = LBound(SheetArray) To UBound(SheetArray)
MsgBox Sheets(SheetArray(x)).Name
Next
End Sub

Mike

 

[Norman Goldsmith]

Replying to the group seems to be the easiest way to simultaneously thank
Tim, Jim and Ron for their rapid help. Glad I posted the code so Tim could
point out the error.

I used Jim Cone's first formulation as it taught me another piece of syntax
"ReDim SheetArray(2 To SheetKtr)"; that clarified what I needed.

Thanks again
Norm