Excel: Print Sheets Selected by a User to a Single PDF

Question

I have a procedure that prints separate PDFs for each worksheet that a user selects (by checking a command button) on a "Glossary" sheet in the same workbook. It works fine, but I would like to have these all printed to a single PDF--not separate files. Can anyone help. Thanx!

Sub Print_PDFs_Click()
'
' This macro saves sheets selected on the Glossary sheet as 
PDF files in the same directory where this file is located
'
Dim RelativePath As String
Dim SheetNumber As Integer
Dim SheetName As String
'
' Open a user dialog to select the folder where the PDFs will be printed
Dim FolderName As String
    With Application.FileDialog(msoFileDialogFolderPicker)
        .AllowMultiSelect = False
        .Show
        On Error Resume Next
        FolderName = .SelectedItems(1)
        Err.Clear
    On Error GoTo 0
End With
'
' Processes all sheets in the workbook
' NOTE: for the line below, SheetNumber = x, where x is the first sheet that is to be printed _
    and ActiveWorkbook.Sheets.Count - y, where y = number of sheets at the end NOT to be printed
     For SheetNumber = 5 To ActiveWorkbook.Sheets.Count - 2
    Sheets(SheetNumber).Activate
'
' Only print the sheets that are checked on the Glossary sheet
' NOTE: for the line below, SheetNumber - z, where z = x (above) - 1
        If Worksheets("Glossary").CheckBoxes("CheckBox" & SheetNumber - 4).Value = 1 Then
            Name = FolderName & "\" & ActiveSheet.Name & ".pdf"
            ActiveSheet.ExportAsFixedFormat Type:=xlTypePDF, Filename:=Name, _
                Quality:=xlQualityStandard, IncludeDocProperties:=True, _
                IgnorePrintAreas:=False, OpenAfterPublish:=False
        End If
    Next SheetNumber
'
' Activates the Glossary sheet, displays message that sheets have been printed, and opens Windows Explorer folder where newly printed PDFs are located
    Sheets("Glossary").Activate
    MsgBox "Sheets have been printed!"
    retVal = Shell("explorer.exe " & FolderName & "\", vbNormalFocus)
'
   End Sub

https://stackoverflow.com/questions/14404650/save-multiple-sheets-to-pdf/14407986#14407986 — Tim Williams, Jul 21 '17 at 22:58

score 0 · Answer 1 · answered Jul 21 '17 at 23:46

The following code prepares sheets in a workbook for making a pdf by greatly expanding the margins and paper size. It then transfers all charts from all sheets onto their own chart sheets. Finally, and most importantly for your question, it then selects all sheets and produces one pdf for the whole workbook:

Sub prepareForPDF()
Dim sh As Worksheet, ch As ChartObject
'first set margins wide, paper orientation landscape, and papersize tabloid
For Each sh In ThisWorkbook.Worksheets
  sh.Activate
    With ActiveSheet.PageSetup
        .LeftHeader = ""
        .CenterHeader = ""
        .RightHeader = ""
        .LeftFooter = ""
        .CenterFooter = ""
        .RightFooter = ""
        .LeftMargin = Application.InchesToPoints(0.25)
        .RightMargin = Application.InchesToPoints(0.25)
        .TopMargin = Application.InchesToPoints(0.75)
        .BottomMargin = Application.InchesToPoints(0.75)
        .HeaderMargin = Application.InchesToPoints(0.3)
        .FooterMargin = Application.InchesToPoints(0.3)
        .PrintTitleRows = ""
        .PrintTitleColumns = ""
        .Orientation = xlLandscape
        .PaperSize = xlPaperTabloid 'xlLetter could be used too
    End With
Next sh
'now move all charts on worksheets to their own chart sheets
For Each sh In ThisWorkbook.Worksheets
  For Each ch In sh.ChartObjects
    ch.Activate
    ActiveChart.Location Where:=xlLocationAsNewSheet
  Next ch
Next sh
ThisWorkbook.Sheets.Select
ThisWorkbook.ExportAsFixedFormat (xlTypePDF)
End Sub

Excel: Print Sheets Selected by a User to a Single PDF

1 Answers1