sheets(array).select problem

[Guest]

I've tried various formats of array contents in sheets(array).select, and
always get a subscript out of range error. I've used (1, 2, 3), ("sheet1",
"sheet2", "sheet3"), and others. I DO have multiple sheets, and have
verified that the ones in the array actually exist.

My current code:

Dim mySheet As Worksheet

For Each mySheet In ActiveWorkbook.Sheets

Worksheets(mySheet.Name).Activate

If ActiveSheet.Tab.ColorIndex = 3 Then 'this is a red tab worksheet

If sheetlist = "" Then
sheetlist = mySheet.Index
Else
sheetlist = sheetlist & ", " & mySheet.Index
End If

End If

Next

Sheets(sheetlist).Select

End Sub

Sheetlist contents when it crashes on select statement:

31, 32, 33, 36

But it crashes if I change the code so that sheetlist contents are:

"sheet31, "sheet32", "sheet33", "sheet36"

 

[Bob Phillips]

You need to create an array

Dim mySheet As Worksheet
Dim sheetList
Dim i As Long

ReDim sheetList(1 To ActiveWorkbook.Sheets.Count)
For Each mySheet In ActiveWorkbook.Sheets
If mySheet.Tab.ColorIndex = 3 Then 'this is a red tab worksheet
i = i + 1
arry(i) = mySheet.Name
End If
Next
Sheets(sheetList).Select


--
HTH

Bob Phillips

(there's no email, no snail mail, but somewhere should be gmail in my addy)

 

[Guest]

Didn't work. I get compile error when it hits arry(i) line: sub or function
not defined
Perhaps you meant sheetlist(i)?

But when I do this I get the same error as before when I hit the select
command: susbscript out of range.

Looking at contents of sheetlist array, it is as expected: the first four
values are the four sheet names that have red tabs. After that, the values
are null.

?sheetlist(1)
9.17_2ad
?sheetlist(2)
9.17_2ae
?sheetlist(3)
9.17_2af
?sheetlist(4)
9.20_1b
?sheetlist(5)

What I find really strange about all this is that if I copy and paste the
code from the Excel help file:

Referring to More Than One Sheet
SpecificsUse the Array function to identify a group of sheets. The following
example selects three sheets in the active workbook.

Sub Several()
Worksheets(Array("Sheet1", "Sheet2", "Sheet4")).Select
End Sub

I get the same subscript out of range error.

Other ideas?

 

[Bob Phillips]

Sorry, only had 2000 when I posted, so I didn't test.

Tested now on 2003 it works

Dim mySheet As Worksheet
Dim sheetList
Dim i As Long

For Each mySheet In ActiveWorkbook.Sheets
If mySheet.Tab.ColorIndex = 3 Then 'this is a red tab worksheet
i = i + 1
ReDim sheetList(1 To i)
sheetList(i) = mySheet.Name
End If
Next
If Not IsEmpty(sheetList) Then Sheets(sheetList).Select


--
---
HTH

Bob

(change the xxxx to gmail if mailing direct)

 

[Dave Peterson]

How about a modified version of Bob's routine:

Option Explicit
Sub testme()

Dim mySheet As Worksheet
Dim sheetList As Variant
Dim i As Long

ReDim sheetList(1 To ActiveWorkbook.Sheets.Count)
i = 0 'I like to initialize my variables
For Each mySheet In ActiveWorkbook.Sheets
If mySheet.Tab.ColorIndex = 3 Then 'this is a red tab worksheet
i = i + 1
sheetList(i) = mySheet.Name
End If
Next
If i > 1 Then
ReDim Preserve sheetList(1 To i)
Sheets(sheetList).Select
Else
MsgBox "no sheets found"
End If

End Sub

 

[Guest]

Marvelous. Thanks to both of you.

How about a modified version of Bob's routine:

Option Explicit
Sub testme()

Dim mySheet As Worksheet
Dim sheetList As Variant
Dim i As Long

ReDim sheetList(1 To ActiveWorkbook.Sheets.Count)
i = 0 'I like to initialize my variables
For Each mySheet In ActiveWorkbook.Sheets
If mySheet.Tab.ColorIndex = 3 Then 'this is a red tab worksheet
i = i + 1
sheetList(i) = mySheet.Name
End If
Next
If i > 1 Then
ReDim Preserve sheetList(1 To i)
Sheets(sheetList).Select
Else
MsgBox "no sheets found"
End If

End Sub