Print multiple ranges from multiple sheets to PDF

Question

I've done a lot of research on this site and haven't found a solution to my problem. I have a workbook with multiple sheets that have specific ranges that need to be printed. Unfortunately these ranges need to printed in a very specific order for a monthly report and it's easier to keep them on separate tabs. I created a code that allows me to print them to my office printer, but I'd like to be able to combine the sheets and then print them directly to PDF. Can anyone help me out? I can continue to use the print macro if not.

Thanks!

Questions/Thoughts

1) Am I able to select the ranges, and print all of them to a single pdf in one go?

2) if not perhaps its easier to print to two different pdfs (based on tab/worksheet) and then combine in adobe

Here's my code:

Sub PrintOut()

Answer = MsgBox("Ahoy scallywag! Would ye like t' print scroll?",
vbYesNoCancel + vbInformation, "Application Message")

If Answer = vbYes Then Else Exit Sub

Application.ScreenUpdating = False

With ActiveSheet.PageSetup

.LeftMargin = Application.CentimetersToPoints(0.5)
.RightMargin = Application.CentimetersToPoints(0.5)
.TopMargin = Application.CentimetersToPoints(0.5)
.BottomMargin = Application.CentimetersToPoints(0.5)
.HeaderMargin = Application.CentimetersToPoints(0.2)
.FooterMargin = Application.CentimetersToPoints(0.2)
Worksheets("PTD and YTD Spend").PageSetup.CenterHorizontally = True
Worksheets("Leasing Detail").PageSetup.CenterHorizontally = True
Worksheets("PTD and YTD Spend").PageSetup.CenterVertically = True
Worksheets("Leasing Detail").PageSetup.CenterVertically = True

Sheets("PTD and YTD Spend").Select
ActiveSheet.PageSetup.PrintArea = "$G$3:$AG$32"
ActiveWindow.SelectedSheets.PrintOut

Sheets("Leasing Detail").Select
ActiveSheet.PageSetup.PrintArea = "$G$3:$AD$32"
ActiveWindow.SelectedSheets.PrintOut

Sheets("PTD and YTD Spend").Select
ActiveSheet.PageSetup.PrintArea = "$G$33:$AG$63"
ActiveWindow.SelectedSheets.PrintOut

Sheets("Leasing Detail").Select
ActiveSheet.PageSetup.PrintArea = "$G$33:$AD$63"
ActiveWindow.SelectedSheets.PrintOut

Sheets("PTD and YTD Spend").Select
ActiveSheet.PageSetup.PrintArea = "$G$64:$AG$93"
ActiveWindow.SelectedSheets.PrintOut

Sheets("Leasing Detail").Select
ActiveSheet.PageSetup.PrintArea = "$G$64:$AD$93"
ActiveWindow.SelectedSheets.PrintOut

Sheets("PTD and YTD Spend").Select
ActiveSheet.PageSetup.PrintArea = "$G$94:$AG$123"
ActiveWindow.SelectedSheets.PrintOut

Sheets("Leasing Detail").Select
ActiveSheet.PageSetup.PrintArea = "$G$94:$AD$123"
ActiveWindow.SelectedSheets.PrintOut

Sheets("PTD and YTD Spend").Select
ActiveSheet.PageSetup.PrintArea = "$G$124:$AG$153"
ActiveWindow.SelectedSheets.PrintOut

Sheets("Leasing Detail").Select
ActiveSheet.PageSetup.PrintArea = "$G$124:$AD$153"
ActiveWindow.SelectedSheets.PrintOut

Sheets("PTD and YTD Spend").Select
ActiveSheet.PageSetup.PrintArea = "$G$154:$AG$183"
ActiveWindow.SelectedSheets.PrintOut

Sheets("Leasing Detail").Select
ActiveSheet.PageSetup.PrintArea = "$G$154:$AD$183"
ActiveWindow.SelectedSheets.PrintOut

Sheets("PTD and YTD Spend").Select
ActiveSheet.PageSetup.PrintArea = "$G$184:$AG$216"
ActiveWindow.SelectedSheets.PrintOut

Sheets("Leasing Detail").Select
ActiveSheet.PageSetup.PrintArea = "$G$184:$AD$213"
ActiveWindow.SelectedSheets.PrintOut

Sheets(Array("PTD and YTD Spend", "Leasing Detail")).Select

ActiveWindow.SelectedSheets.PrintOut
Sheets("PTD and YTD Spend").Select 'Needed so that multiple sheets don't remain selected

End With
Application.ScreenUpdating = True

End Sub

Combine all your ranges into one worksheet, then print the worksheet to a .pdf — GMalc, Jan 17 '18 at 03:56

score 0 · Answer 1 · answered Jan 17 '18 at 05:03

You could use two alternative strategies:

Export each range to pdf using a sequence of file names.
Concatenate all pdf files, with some tool (e.g., pdftk).

or

Create a sequence of worksheets, one for each range you want to print. You can do this by duplicating the source worksheet and either removing what you do not need, or if that introduces formula errors or other issues setting the print area.
Form an array with the names of the worksheets, and printout the array.

score 0 · Answer 2 · answered Jan 18 '18 at 02:20

How about this option?

Sub CreatePDF()

    ActiveWorkbook.Sheets(Array("Sheet1", "Sheet2", "Sheet3")).Select

    Sheets("Sheet1").Activate

    ActiveSheet.ExportAsFixedFormat _
            Type:=xlTypePDF, _
            Filename:="C:\temp.pdf", _
            Quality:=xlQualityStandard, _
            IncludeDocProperties:=True, _
            IgnorePrintAreas:=False, _
            OpenAfterPublish:=True

    Sheets("Sheet1").Select
    Range("A1").Select

End Sub

I'm pretty sure there are ways to customize it, and/or enhance it.

Print multiple ranges from multiple sheets to PDF

2 Answers2