How to Create an array

[JB]

I would like to Create an array containing Sheets(3) to Sheets.Count
Is this possible .. if so please advise

Regards & TIA

 

[SteveM]

I would like to Create an array containing Sheets(3) to Sheets.Count
Is this possible .. if so please advise

Regards & TIA

You already have a built in "array" with the Worksheets collection.
Create a worksheet variable and use that to cycle through the
collection using the For Each construct. I.e.,

Sub SheetDiddle()
Dim ws As Worksheet

For Each ws In ThisWorkbook.Worksheets
If ws.Index >= 3 Then
' Do something here
End If
Next

End Sub


SteveM

 

[JB]

You already have a built in "array" with the Worksheets collection.
Create a worksheet variable and use that to cycle through the
collection using the For Each construct. I.e.,

Sub SheetDiddle()
Dim ws As Worksheet

For Each ws In ThisWorkbook.Worksheets
If ws.Index >= 3 Then
' Do something here
End If
Next

End Sub


SteveM

Hmmm ... what I was hoping to do was group the worksheets together and then
print them in one operation

Is this possible

 

[Chip Pearson]

Try

Dim Arr() As String
Dim N As Long
With ThisWorkbook.Worksheets
ReDim Arr(1 To .Count)
For N = 1 To .Count
Arr(N) = .Item(N).Name
Next N
End With


--
Cordially,
Chip Pearson
Microsoft Most Valuable Professional
Excel Product Group, 1998 - 2008
Pearson Software Consulting, LLC
www.cpearson.com
(email on web site)

 

[JB]

I think this is how I should do it ... but need to add sheets up to
Sheets.Count

Sheets(Array(3, 4, 5, 6)).PrintOut

 

[JB]

Thanks Chip ... exactly what I requied

Try

Dim Arr() As String
Dim N As Long
With ThisWorkbook.Worksheets
ReDim Arr(1 To .Count)
For N = 1 To .Count
Arr(N) = .Item(N).Name
Next N
End With


--
Cordially,
Chip Pearson
Microsoft Most Valuable Professional
Excel Product Group, 1998 - 2008
Pearson Software Consulting, LLC
www.cpearson.com
(email on web site)

 

[Peter T]

Dim i as Long
ReDim arr(3 To ActiveWorkbook.Sheets.Count)
For i = 3 To UBound(arr)
arr(i) = i
Next
ActiveWorkbook.Sheets(arr).Printout

Assumes of course at least 3 sheets.

Regards,
Peter T

 

[JB]

Got it now ... Thanks for your input

Dim i as Long
ReDim arr(3 To ActiveWorkbook.Sheets.Count)
For i = 3 To UBound(arr)
arr(i) = i
Next
ActiveWorkbook.Sheets(arr).Printout

Assumes of course at least 3 sheets.

Regards,
Peter T