Grouping and Printing Worksheets in Excel VBA

[Johnny]

Hi all,

I'm trying to create a simple sub that will group a series of sheet
and print out the group. For example, if I have sheet1, sheet2, and
sheet3, I want the sub to select all three sheets and print them out
(not cycle them but select them.)

So, I can do this:

Sub Foo()
Worksheets(Array("Sheet1","Sheet2","Sheet3")).Printout
End Sub

but I can't understand why I can't do this:

Sub Foo2()
Dim i as Integer
Dim strSheets() as String
Dim r as Range

Set r = Range("ListOfSheetToPrintIsHere")

With r
ReDim strSheets(0 to .Cells.Count-1)
For i = 0 to .Rows.Count-1
strSheets(i)=.Cells(i).Value
Next
End With

Worksheets(strSheets).PrintOut

Erase strSheets()
Set r = Nothing
End Sub

I get an subscript out of range error on the Worksheets(strSheets....
line. I can't simply use the array functions for the sheets I want to
select and print because I don't know at design time what sheets the
user will want to group print.

Thanks,
Johnny

 

[Johnny]

Please disregard this post. Turns out that one of the worksheets in
the list had the incorrect name. You can, in fact, pass an array of
strings to the Worksheets object, if you created it manually or
dynamically.

Thanks,
Johnny