VBA to combine individual print jobs into 1 print stream

[TheNoocH]

I have a workbook with a worksheet (Reports) listing all the reports I want to print
i have another worksheet (Calcs) which has all formulas pulling from other data sheets...this sheet has a validation drop down reading from the list of reports on the reports tab...once you change the drop down the formulas bring over the proper data...

so say i have 5 reports the current code i have will send it as 5 individual print jobs...is there a way to queue up the print jobs and then send all 5 as 1 combined print job?
NOTE: i'm trying to print to pdf and looking for 1 pdf file and not 5...

here is the current vba loop that is printing the reports

For Each cell In shtReports.Range(shtReports.Cells(2, "A"), shtReports.Cells(shtReports.Cells(Rows.Count, "A").End(xlUp).Row, "A"))
            shtCalcs.Range("D1").Value = cell.Value
            shtCalcs.PrintOut copies:=1
Next

thanks in advance for any thoughts

 

[Worf]

One easy way is to use a helper sheet:

Sub OnePdf()
Dim rep As Worksheet, clc As Worksheet, cell As Range, ash As Worksheet, i%
Set ash = Sheets("Aux")
Set clc = Sheets("Calcs")
Set rep = Sheets("Reports")
ash.Cells.ClearContents
i = 0
For Each cell In rep.Range(rep.Cells(2, "A"), rep.Cells(rep.Cells(Rows.Count, "A").End(xlUp).Row, "A"))
    clc.[d1] = cell
    clc.UsedRange.Copy ash.Cells(1 + i * 50, 1)
    i = i + 1
Next
'ash.PrintOut
ash.PrintPreview
End Sub