VBA Save as PDF in Excel — ExportAsFixedFormat for Sheets, Ranges and Workbooks

TL;DR — There is no "SaveAs PDF" in VBA. You call ExportAsFixedFormat on a sheet, a range or a workbook, pass Type:=xlTypePDF and a full path ending in .pdf, and Excel renders exactly the pages it would print. That is the whole idea: a PDF is a print job sent to a file. So the print area, orientation and fit-to-page settings decide what ends up in the PDF — set the page first, then export.

Sub SaveReportAsPdf()
    Dim pdfPath As String
    pdfPath = ThisWorkbook.Path & "\Report_" & Format(Date, "yyyy-mm-dd") & ".pdf"
    ThisWorkbook.Worksheets("Report").ExportAsFixedFormat _
        Type:=xlTypePDF, Filename:=pdfPath, Quality:=xlQualityStandard, _
        IgnorePrintAreas:=False, OpenAfterPublish:=False
End Sub

Most people arrive here after trying SaveAs with a .pdf name, or after recording a macro of File → Export and getting a line they do not understand. This guide is the lead of a three-part cluster on getting output out of Excel. The three pieces share one pipeline. Page Setup decides what a page is — its print area, orientation and scale. PrintOut sends those pages to a printer. ExportAsFixedFormat, covered here, sends the same pages to a PDF file. Get the page right once and both outputs come out right; get it wrong and no export option will rescue it.

What you'll learn

• Why a PDF from Excel is a print job, and what that means for your code
• The ExportAsFixedFormat call and the arguments that actually matter
• Exporting one sheet, one range, or the whole workbook
• Putting several chosen sheets into a single PDF
• Building a safe file path, and the run-time error 1004 a bad one causes
• The judgment call: ExportAsFixedFormat versus "printing to PDF"

The mental model: a PDF is a print sent to a file

Excel does not have a separate PDF layout. When you export, it paginates the sheet exactly as it would for paper — same print area, same page breaks, same margins, same header and footer — and writes those pages into a file instead of spooling them to a printer. That single fact explains nearly every "why does my PDF look like that" question:

• The PDF cuts a wide table across two pages because the page is portrait at 100% scale.
• The PDF includes a stray block of cells far below the data because the used range reaches that far.
• The PDF shows only part of the sheet because a print area was set months ago and never cleared.

None of these is an export problem. They are page problems, and they are fixed in Page Setup, before the export line runs.

The ExportAsFixedFormat call

ExportAsFixedFormat exists on a Worksheet, a Range, a Workbook and a Chart. The arguments you will actually use:

Use named arguments. The positional form works, but ExportAsFixedFormat xlTypePDF, p, , , True is unreadable a week later, and the order is easy to get wrong.

One sheet, one range, or the whole workbook

The object you call it on decides the scope:

' One sheet (respects that sheet's print area)
Worksheets("Report").ExportAsFixedFormat Type:=xlTypePDF, Filename:=pdfPath

' One range, regardless of the print area
Worksheets("Report").Range("A1:H40").ExportAsFixedFormat Type:=xlTypePDF, Filename:=pdfPath

' Every visible sheet in the workbook, in tab order
ThisWorkbook.ExportAsFixedFormat Type:=xlTypePDF, Filename:=pdfPath

The range form is the quickest way to ship "just this block" without touching the sheet's print area. The workbook form leaves out hidden sheets, which is usually what you want for helper sheets and lookup tables.

Several chosen sheets into one PDF

To combine a specific set of sheets — say a summary and two detail tabs — select them as a group, then export the active sheet. A grouped selection exports as one document:

Sub ExportChosenSheets(pdfPath As String)
    ThisWorkbook.Worksheets(Array("Summary", "North", "South")).Select
    ActiveSheet.ExportAsFixedFormat Type:=xlTypePDF, Filename:=pdfPath
    ThisWorkbook.Worksheets("Summary").Select      ' ungroup again
End Sub

This is one of the few places where Select is genuinely needed; see Select and Activate for why you avoid it elsewhere. Always ungroup afterwards: a user who types into grouped sheets writes into all of them at once.

For one PDF per sheet, loop instead and build a name from each sheet:

Dim ws As Worksheet
For Each ws In ThisWorkbook.Worksheets
    If ws.Visible = xlSheetVisible Then
        ws.ExportAsFixedFormat Type:=xlTypePDF, _
            Filename:=ThisWorkbook.Path & "\" & ws.Name & ".pdf"
    End If
Next ws

A safe file path, and the error 1004 it prevents

The failure everyone hits is run-time error 1004: Document not saved. The document may be open, or an error may have been encountered when saving. It almost never means what it seems to. The usual causes:

• The folder does not exist — Excel will not create it. Make it first with MkDir.
• The PDF is already open in a viewer, which locks the file.
• The file name contains a character Windows forbids, such as / from a date or : from a time.
• There is nothing to print — the sheet or print area is empty.

A bare file name with no folder is not an error, which is worse: the PDF lands in Excel's current folder (see CurDir), and the user never finds it. So always build the full path, and clean the parts that come from data:

Function SafePdfPath(folder As String, baseName As String) As String
    Dim bad As Variant
    For Each bad In Array("/", "\", ":", "*", "?", """", "<", ">", "|")
        baseName = Replace(baseName, bad, "-")
    Next bad
    If Dir(folder, vbDirectory) = "" Then MkDir folder
    SafePdfPath = folder & "\" & baseName & ".pdf"
End Function

If the user should choose the location, ask with GetSaveAsFilename and pass its result as Filename — and check for False, which means they cancelled.

The judgment call: export instead of printing to PDF

You will find code that sets the active printer to Microsoft Print to PDF and calls PrintOut with a file name. Do not copy it. It depends on a printer name that differs by machine and language, it can pop a Save dialog that stalls an unattended macro, and it changes the user's active printer as a side effect. ExportAsFixedFormat is built into Excel, needs no printer at all, and takes the path as an argument. The rule is simple: paper goes through PrintOut; files go through ExportAsFixedFormat. And whichever you call, the page it renders was decided in Page Setup — that is where a wrong-looking PDF gets fixed.

How ExcelMaster helps

PDF macros fail in dull, specific ways: a folder that does not exist yet, a date with slashes in the file name, a print area left over from last quarter, a table that spills onto a second page. Each one shows up as the same unhelpful error 1004 or a PDF that looks subtly wrong.

ExcelMaster lets you say what you want — "export the Summary and both region tabs into one PDF named after this month, landscape, one page wide" — and it writes the page setup, the safe path and the export together, then checks the result. You keep the macro and skip the guesswork.

Frequently asked questions

How do I save an Excel sheet as a PDF with VBA?

Call ExportAsFixedFormat on the sheet with Type:=xlTypePDF and a full Filename ending in .pdf, for example Worksheets("Report").ExportAsFixedFormat Type:=xlTypePDF, Filename:="C:\Out\Report.pdf". There is no PDF option in SaveAs; the export method is the supported way.

How do I save only a range as a PDF?

Call the method on the range itself: Range("A1:H40").ExportAsFixedFormat Type:=xlTypePDF, Filename:=pdfPath. This exports that block regardless of the sheet's print area, so you do not have to change and restore the print area around the export.

How do I export multiple sheets into one PDF?

Select the sheets as a group with Worksheets(Array("Summary", "North")).Select, then call ActiveSheet.ExportAsFixedFormat. The grouped selection is written as one document. Select a single sheet afterwards to ungroup, so later typing does not land on every sheet.

Why does ExportAsFixedFormat raise run-time error 1004?

Usually because the folder in the path does not exist, the PDF is already open in a viewer, the file name contains a forbidden character such as / or :, or there is nothing to print. Build the full path, create the folder first, and strip illegal characters from any part that comes from data.

Why does my PDF cut the table across pages or include extra cells?

Because the PDF uses the sheet's page setup. A wide table in portrait at 100% scale breaks across pages, and a used range that reaches far below the data adds blank pages. Fix it in PageSetup — orientation, print area, and fit to one page wide — before you export.

Tested in

Tested in: Excel 365 (Windows 11), VBA 7.1 — last verified 2026-09-29.

Related guides: VBA PrintOut · VBA Page Setup · VBA GetSaveAsFilename · VBA MkDir · VBA CurDir · VBA Outlook · VBA On Error