PDF

Last Updated on January 22, 2022 – 2 min read

How can you save sheets or workbooks as PDF using a VBA macro?

To create a report or a presentation based on Excel data the PDF format can be more readable for your users.

You will discover in this article, how to export sheets, workbooks, range of cells, or Excel charts into a PDF.

Lac Vert, Passy, France – Photo taken by me

Export a Sheet as PDF in VBA

You can save the document in PDF using the ExportAsFixedFormat VBA method. As an argument, you have to specify xlTypePDF for the parameter type.

Sub SaveSheetPDF()
'Save one sheet as PDF

   ThisWorkbook.Sheets("Weather").Cells.ExportAsFixedFormat _
      Type:=xlTypePDF, FileName:="Desktop", Quality:=xlQualityStandard, _
         IncludeDocProperties:=True, IgnorePrintAreas:=False, _
            OpenAfterPublish:=True

End Sub

Export Sheets as PDF in VBA

To export multiple sheets to PDF, you can create a sheet table and use the ExportAsFixedFormat method as previously.

Sub SaveSeveralSheetsPDF()

    Sheets("Maps").Range("A1:I49").Select 'Select the range
    Sheets("Weather").Activate 'Active the other sheet
    ActiveSheet.Range("A1:I49").Select ' Select the range
    Sheets(Array("Maps", "Weather")).ExportAsFixedFormat _ 
       Type:=xlTypePDF, FileName:="Desktop", _
          Quality:=xlQualityStandard,IncludeDocProperties:=True, IgnorePrintAreas:=False, _
             OpenAfterPublish:=True

End Sub

Export all Sheets separately as PDF in VBA

Below is a procedure with a For Each loop to export each sheet to a PDF.

Sub SaveSheetsPDF()

    Dim Sh As Sheet
    
    For each Sh in ActiveWorbook.Sheets
       Sh.ExportAsFixedFormat Type:=xlTypePDF, _
          FileName:=Sh.Name, Quality:=xlQualityStandard, _
             IncludeDocProperties:=True, IgnorePrintAreas:=False, _
                OpenAfterPublish:=True
    Next Sh

End Sub

Export a Workbook as PDF in VBA

You can also use the ExportAsFixedFormat method directly on a workbook.

Sub SaveWbkPDF()

    ActiveWorkbook.ExportAsFixedFormat Type:=xlTypePDF, _
       FileName:="Desktop", Quality:=xlQualityStandard, _
          IncludeDocProperties:=True, IgnorePrintAreas:=False, _
             OpenAfterPublish:=True
End Sub

Export a Range as PDF in VBA

You can also export to PDF a well-defined range of cells.

Sub SaveWbkPDF()

    ActiveSheet.Range("A1:C40").ExportAsFixedFormat Type:=xlTypePDF, _
       FileName:="Desktop", Quality:=xlQualityStandard, _
          IncludeDocProperties:=True, IgnorePrintAreas:=False, _
             OpenAfterPublish:=True
End Sub

Export a Chart as PDF in VBA

You can export only a chart object by using the ExportAsFixedFormat method.

Sub SaveChartPDF()

    ActiveChart.ExportAsFixedFormat Type:=xlTypePDF, _
       FileName:="Desktop", Quality:=xlQualityStandard, _
          IncludeDocProperties:=True, IgnorePrintAreas:=False, _
              OpenAfterPublish:=True
End Sub

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s