sheets(array).select problem
 

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 a

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

sheets(array).select problem
 

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)

"Mark" wrote in message
...
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 a

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

sheets(array).select problem
 

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" wrote:

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)

"Mark" wrote in message
...
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 a

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

sheets(array).select problem
 

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)


"Mark" wrote in message
...
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" wrote:

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)

"Mark" wrote in message
...
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 a

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

sheets(array).select problem
 

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

Mark wrote:

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" wrote:

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)

"Mark" wrote in message
...
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 a

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





--

Dave Peterson

sheets(array).select problem
 

Marvelous. Thanks to both of you.

"Dave Peterson" wrote:

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

Mark wrote:

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" wrote:

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)

"Mark" wrote in message
...
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 a

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





--

Dave Peterson