Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Syntax problem - select many sheets at once
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Syntax problem - select many sheets at once
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 -- Tim Zych http://www.higherdata.com Workbook Compare - Excel data comparison Free & Pro versions "Norman Goldsmith" 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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Syntax problem - select many sheets at once
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" 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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Syntax problem - select many sheets at once
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 -- Regards Ron de Bruin http://www.rondebruin.nl/tips.htm "Norman Goldsmith" 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 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Syntax problem - select many sheets at once
-or-
'-- Sub ZtoA() Dim I As Long Sheets(2).Select For I = 3 To Sheets.Count Sheets(I).Select False Next End Sub -- Jim Cone Portland, Oregon USA |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Syntax problem - select many sheets at once
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" wrote: 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 |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
Syntax problem - select many sheets at once
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 "Norman Goldsmith" 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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Newbie: Problem with 'Select Case' testing syntax | Excel Programming | |||
Select Case syntax | Excel Programming | |||
sheets(array).select problem | Excel Discussion (Misc queries) | |||
unprotecting sheets in another workbook (syntax problem?) | Excel Programming | |||
Select Case syntax | Excel Programming |