Print multipage document from loop

mmmr · Sep 11th 2018

I have some code to print from a loop and this works fine apart from it's making a new document from each loop. What I want is to be able to print all pages to a single document. Here is the code I have

Code

Dim lStart As Date, lEnd As Date, lInterval As Integer
  Dim lPrint As Long


  lStart = Date
  lEnd = Date + 7
  lInterval = 1


  For lPrint = lStart To lEnd Step lInterval
    [A1] = lPrint
    ActiveSheet.PrintOut
  Next lPrint

Display More

I'm printing to a pdf creator btw so that I can check for errors before printing to paper

**Kenneth Hobson** · Sep 11th 2018

The better option might be to:
1. Create new workbookbook.
2. Copy each sheet after [A1] changed after last sheet in new workbook.
3. Delete sheet1 in new workbook.
4. Select All Sheets.
5. Export ActiveSheet (which now prints all) to a pdf file.
6. Close and save or delete new workbook.
7. Open multipage pdf file.

Of course with pdfcreator, you could merge all pdf files had you exported each sheet to a pdf file. It seems tedious to use pdfcreator as the printer for each sheet though. The 7 steps is how I would do it. That way, you skip the need for a 3rd party application other than to Open the pdf(s).

mmmr · Sep 11th 2018

Thanks. From all my google searches it seems unlikely that there's an obvious solution. I was hoping not to have to create multiple sheets or pages. I'm wondering if it's something maybe better suited to C# or something.

ashu1990 · Sep 12th 2018

see if this helps

Code

Sub test()


For Each i In ThisWorkbook.Sheets


With Worksheets(i.Name)
    With .PageSetup
        .PrintArea = "$C$2:$L$60"
        .Orientation = xlPortrait
        .Zoom = 50
        .FitToPagesWide = 1
        .FitToPagesTall = False
        .LeftMargin = Application.InchesToPoints(0)
        .RightMargin = Application.InchesToPoints(0)
        .TopMargin = Application.InchesToPoints(0)
        .BottomMargin = Application.InchesToPoints(0)
        .HeaderMargin = Application.InchesToPoints(0)
        .FooterMargin = Application.InchesToPoints(0)
    End With
    .PrintOut
End With




Next i


End Sub

Display More

Print multipage document from loop

Participate now!

Share