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