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

Regards & TIA

On Feb 2, 9:16 am, "JB" wrote:
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

"SteveM" wrote in message
...
On Feb 2, 9:16 am, "JB" wrote:
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

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

Is this possible

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

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

Got it now ... Thanks for your input

"Peter T" <peter_t@discussions wrote in message
...
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" wrote in message
...
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

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" wrote in message
...
I would like to Create an array containing Sheets(3) to Sheets.Count
Is this possible .. if so please advise

Regards & TIA

Thanks Chip ... exactly what I requied

"Chip Pearson" wrote in message
...
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" wrote in message
...
I would like to Create an array containing Sheets(3) to Sheets.Count
Is this possible .. if so please advise

Regards & TIA