Excel – Save certain sheets as PDF

microsoft excelpdf

I have an excel workbook, where the end user does some selection, based on which the sheets which needs to be saved as a single PDF, are enlisted in Sheet 1, cell B10 like this (E1, E2, E3, E4…)

Now I have tried this macro below, but it does not work properly, the reasons are given below the code:

Sub pdff()

   Sheets("E1").Activate
   ActiveSheet.UsedRange.Select
   Sheets("E2").Activate
   ActiveSheet.UsedRange.Select
   Sheets("E3").Activate
   ActiveSheet.UsedRange.Select

   ThisWorkbook.Sheets(Array("E1", "E2","E3")).Select
   Selection.ExportAsFixedFormat Type:=xlTypePDF, Filename:= _
      "C:\Users\Damon\Desktop\pdfmaker.pdf", Quality:=xlQualityStandard, _
      IncludeDocProperties:=True, IgnorePrintAreas:=False, OpenAfterPublish:= _
      True
End Sub

The problems with the code:

  • After the code runs, it groups the sheets together, which has to be
    ungrouped manually.
  • The code saves the areas outside of the set print area (which does
    not happen if I save each sheet manually as PDF)
  • The code is not dynamic, it does not pick up the sheet names from
    cell B10

Best Answer

With:

enter image description here

This version will meet all your goals:

Sub pdff()
    Dim s As String, ary, a, sh As Worksheet

    Set sh = ActiveSheet
    s = Sheets("Sheet1").Range("B10").Text
    ary = Split(s, ",")

    For Each a In ary
        Sheets(a).Select
        Sheets(a).Activate
        Sheets(a).Range(ActiveSheet.PageSetup.PrintArea).Select
    Next a

    ThisWorkbook.Sheets(ary).Select

    Selection.ExportAsFixedFormat Type:=xlTypePDF, Filename:= _
        "C:\Users\Damon\Desktop\pdfmaker.pdf", Quality:=xlQualityStandard, _
        IncludeDocProperties:=True, IgnorePrintAreas:=False, OpenAfterPublish:= _
        True

    sh.Select
End Sub

Just make sure you have a Sheet1 as well as the E1 sheets.

Related Question