Can I include noncontiguous worksheet in array of worksheets?
 

The following code selects contiguous worksheets 5 through 11.

============

Sub SelectSheets()

Dim Sh() As Variant, wks As Worksheet, i As Integer

i = 0

For Each wks In ActiveWorkbook.Sheets
If wks.Range("E2") = 1 Then
i = i + 1
ReDim Preserve Sh(1 To i)
Sh(i) = wks.Name
End If
Next
Worksheets(Sh()).Select

End Sub

===============

I would like to add one noncontiguous worksheet (#32) to the array of
selected worksheets. Is there a way to do so?


Barney Byrd

Can I include noncontiguous worksheet in array of worksheets?
 

Hi, try this...
Worth noting the If you absolutely want the Array to start at 1 not 0
then move the line i=i+1 above the Redim Preserve Statement..

Sub SelectSheets()
Dim Sh() As Long, wks As Worksheet, i As Integer
For Each wks In ActiveWorkbook.Sheets
If wks.Range("E2").Value = 1 Or wks.Index = 32 Then
ReDim Preserve Sh(i) '''No Need for 1 to i.
'''If you want your arrays to start at 1 then you can use Option Base 1
'''in Declarations section of code
Sh(i) = wks.Index
i = i + 1
End If
Next
Worksheets(Sh()).Select
End Sub


Hth,
O

Can I include noncontiguous worksheet in array of worksheets?
 

Barney,

Something like this...
'---------------------------------
Sub SelectSheets()
Dim Sh() As String
Dim wks As Worksheet
Dim i As Long

ReDim Sh(1 To Sheets.Count)

For Each wks In ActiveWorkbook.Sheets
If wks.Range("E2") = 1 Then
i = i + 1
Sh(i) = wks.Name
End If
Next
Sh(i + 1) = Sheets(32).Name
ReDim Preserve Sh(1 To i + 1)
Worksheets(Sh()).Select

End Sub
'---------------------------------------

Regards,
Jim Cone
San Francisco, USA



"Barney Byrd" wrote in message
...
The following code selects contiguous worksheets 5 through 11.
============
Sub SelectSheets()
Dim Sh() As Variant, wks As Worksheet, i As Integer
i = 0
For Each wks In ActiveWorkbook.Sheets
If wks.Range("E2") = 1 Then
i = i + 1
ReDim Preserve Sh(1 To i)
Sh(i) = wks.Name
End If
Next
Worksheets(Sh()).Select

End Sub
===============
I would like to add one noncontiguous worksheet (#32) to the array of
selected worksheets. Is there a way to do so?
Barney Byrd

Can I include noncontiguous worksheet in array of worksheets?
 

Thanks to both of you, Jim and OJ. With your help I was able to resolve my
problem.


Barney Byrd


"Jim Cone" wrote in message
...
Barney,

Something like this...
'---------------------------------
Sub SelectSheets()
Dim Sh() As String
Dim wks As Worksheet
Dim i As Long

ReDim Sh(1 To Sheets.Count)

For Each wks In ActiveWorkbook.Sheets
If wks.Range("E2") = 1 Then
i = i + 1
Sh(i) = wks.Name
End If
Next
Sh(i + 1) = Sheets(32).Name
ReDim Preserve Sh(1 To i + 1)
Worksheets(Sh()).Select

End Sub
'---------------------------------------

Regards,
Jim Cone
San Francisco, USA



"Barney Byrd" wrote in message
...
The following code selects contiguous worksheets 5 through 11.
============
Sub SelectSheets()
Dim Sh() As Variant, wks As Worksheet, i As Integer
i = 0
For Each wks In ActiveWorkbook.Sheets
If wks.Range("E2") = 1 Then
i = i + 1
ReDim Preserve Sh(1 To i)
Sh(i) = wks.Name
End If
Next
Worksheets(Sh()).Select

End Sub
===============
I would like to add one noncontiguous worksheet (#32) to the array of
selected worksheets. Is there a way to do so?
Barney Byrd

Can I include noncontiguous worksheet in array of worksheets?
 

Or you can skip the option declaration and explicitly declare where you want
it to start like you were already doing. Obviously such choices represent
personal preference.

--
Regards,
Tom Ogilvy

"OJ" wrote in message
oups.com...
Hi, try this...
Worth noting the If you absolutely want the Array to start at 1 not 0
then move the line i=i+1 above the Redim Preserve Statement..

Sub SelectSheets()
Dim Sh() As Long, wks As Worksheet, i As Integer
For Each wks In ActiveWorkbook.Sheets
If wks.Range("E2").Value = 1 Or wks.Index = 32 Then
ReDim Preserve Sh(i) '''No Need for 1 to i.
'''If you want your arrays to start at 1 then you can use Option Base 1
'''in Declarations section of code
Sh(i) = wks.Index
i = i + 1
End If
Next
Worksheets(Sh()).Select
End Sub


Hth,
O