Sheet Array for Printing

[sdohertyccb]

I am trying to write code that will find a dynamic array of sheets in a workbook and collect those to print, starting with Sheet(5) and finding all sheets after that. The number of sheets will change for each client depending on the number of salesmen for each. Any help you can give will be greatly appreciated. Thanks in advance for your help.

 

[Marcelo Branco]

As a starter maybe something like

Sub testPrint()
    Dim i As Long
 
    With ThisWorkbook
        If .Worksheets.Count < 5 Then Exit Sub
 
        For i = 5 To .Worksheets.Count
            'do your stuff like .Worksheets(i).PrintOut
            Debug.Print .Worksheets(i).Name
        Next i
    End With
 
End Sub

M.

 

[sdohertyccb]

Thanks Marcelo for the quick reply.
I am wanting to print these out as a batch, so they will be in a single pdf file. I have the code for the pdf, but haven't been able to set the report array for all of the sheets.
I am trying something like this:

ReportArray = Sheets(Array(Sheet5, Sheet6, Sheet7...))
Worksheets (ReportArray).PrintOut

 

[Marcelo Branco]

Well, i am not sure if this

ReportArray = Sheets(Array(Sheet5, Sheet6, Sheet7...)
Worksheets (ReportArray).PrintOut

is possible


Maybe someone else can help you

M.

 

[sdohertyccb]

Can anyone else help me with this problem? This is the final piece of a big project and am hoping to find a resolution soon....
Thanks for helping me out.

 

[sdohertyccb]

Just in case anyone needs the answer to this problem, here is how I solved it:

Dim i as Integer
For i = 1 to ActiveWorkbook.Worksheets.Count Step 1
If i > 9 Then   'I want to start collecting sheets AFTER Sheet9
Sheets(i).Select Replace:=False
Else: End If
Next i
Application.Dialogs(xlDialogPrint).Show