VBA code to save Excel files as PDF

Since Excel 2010 it has been possible to save Excel files as PDF documents.  The code below provides the VBA options to do achieve this.  These VBA Code Snippets can be used in much larger processes to automate the distribution of hundreds of document in just a few seconds.

Saving Excel workbooks, sheets, charts and ranges as PDF documents

'Save Active Sheet(s) as PDF
ActiveSheet.ExportAsFixedFormat Type:=xlTypePDF, _
    Filename:="C:\Users\marks\Documents\Saved PDF.pdf"

'Save active workbook as PDF
ActiveWorkbook.ExportAsFixedFormat Type:=xlTypePDF, _
    Filename:="C:\Users\marks\Documents\Saved PDF.pdf"

'Save selection as PDF
Selection.ExportAsFixedFormat Type:=xlTypePDF, _
    Filename:="C:\Users\marks\Documents\Saved PDF.pdf"

'Save a chart as PDF
ActiveChart.ExportAsFixedFormat Type:=xlTypePDF, _
    Filename:="C:\Users\marks\Documents\Saved PDF.pdf"

'Save a range as PDF
Sheets("Sheet1").Range("A1:H20").ExportAsFixedFormat Type:=xlTypePDF, _
    Filename:="C:\Users\marks\Documents\Saved PDF.pdf"

Basic notes for saving PDF documents

• If the Filename property is not provided, the PDF document is saved with the PDF file extension in the same folder and with the same name as the Excel document.
• If the Filename property excludes the file path, but includes a file name, the document is saved into the same folder as the Excel document, but with the specified filename.
• If the Filename property does not include .pdf at the end, it will use the last section of text after the final “\” as the file name and add the .pdf suffix automatically.
• If a PDF already exists in the location with that file name, it will be overwritten.  It may be necessary include file handling procedures to prevent overwriting existing documents and handle errors.

Selecting specific worksheets before saving as PDF

If more than one worksheet is active, the created PDF will include all the active sheets.  The following code selects multiple worksheets before saving the PDF.

'Select specific sheets from workbook, the save all as PDF
Sheets(Array("Sheet1", "Sheet2")).Select
ActiveSheet.ExportAsFixedFormat Type:=xlTypePDF, _
    Filename:="C:\Users\marks\Documents\Saved PDF.pdf"

Loop through each worksheet and save as separate documents

It is possible to loop through each worksheet in the active workbook and save each into its own PDF.

'Loop through all worksheets and save as individual PDF in same folder 
'as the Excel file
Dim ws As Worksheet
For Each ws In ActiveWorkbook.Worksheets
    ws.ExportAsFixedFormat Type:=xlTypePDF, Filename:=ws.Name
Next

Other PDF Export Options

When using ExportAsFixedFormat there are other optional arguments available:

'Open the document after it is saved - options are True / False
OpenAfterPublish:=False

'Include the Excel document properties into the PDF - options are True / False
IncludeDocProperties:=True

'Does the created PDF adhere to the Print Areas already set in the 
'worksheet - options are True / False
IgnorePrintAreas:=False

'Set the output quality of the created document - options are 
'xlQualityMinimum / xlQualityStandard
Quality:=xlQualityStandard

'The page to start printing from. If Excluded, will start from the first page
From:=1

'The page to print to. If Excluded, will go to the last page
To:=2

Example using all the options

'Example using all the options
ActiveSheet.ExportAsFixedFormat _
    Type:=xlTypePDF, _
    Filename:="C:\Users\marks\Downloads\testpage2.pdf", _
    OpenAfterPublish:=False, _
    IncludeDocProperties:=True, _
    IgnorePrintAreas:=False, _
    Quality:=xlQualityStandard, _
    From:=1, To:=2

Other Fixed Formats available

The Type property can also create XPS documents when it is set to xlTypeXPS, rather than xlTypePDF.  XPS is Microsoft’s own fixed file format, which is similar to PDF, but based on the XML language, but is rarely used.