VBAAdvanced7 min read

A VBA Macro That Exports Any Selection as a PDF

A short, reusable macro with proper error handling — paste it in, bind it to a button, and export any sheet or selection to PDF in one click.

Excel can export a sheet or selection to PDF natively (File → Export), but doing that manually every week gets old fast. A short macro turns it into a single click, and — unlike recording a macro — handles the case where the user cancels the save dialog or the export itself fails.

The macro

Sub ExportSelectionToPDF()
    Dim fileName As Variant
    Dim exportRange As Range

    On Error Resume Next
    Set exportRange = Selection
    On Error GoTo 0

    If exportRange Is Nothing Then
        MsgBox "Select a range first.", vbExclamation
        Exit Sub
    End If

    fileName = Application.GetSaveAsFilename( _
        InitialFileName:=ActiveSheet.Name & "_" & Format(Date, "yyyy-mm-dd") & ".pdf", _
        FileFilter:="PDF Files (*.pdf), *.pdf")

    If fileName = False Then Exit Sub  ' user cancelled

    On Error GoTo ExportError
    exportRange.ExportAsFixedFormat _
        Type:=xlTypePDF, _
        Filename:=fileName, 
        Quality:=xlQualityStandard, 
        OpenAfterPublish:=True
    Exit Sub

ExportError:
    MsgBox "Export failed: " & Err.Description, vbCritical
End Sub

Paste into a standard module (Alt+F11 → Insert → Module).

Why each piece is there

  • `On Error Resume Next` around the Selection check — prevents a crash if nothing is selected when the macro runs
  • `GetSaveAsFilename` instead of a hard-coded path — lets the user pick where the PDF goes, and naming it with today's date avoids overwriting last month's export
  • Checking `fileName = False` — this is what happens when the user clicks Cancel on the save dialog; without this check, the macro would error out instead of exiting cleanly
  • A dedicated `ExportError` label — catches real export failures (e.g. the print area is empty) and shows a readable message instead of a cryptic VBA runtime error

Binding it to a button

  1. Developer tab → Insert → Button (Form Control), draw it anywhere on the sheet
  2. In the "Assign Macro" dialog, pick ExportSelectionToPDF
  3. Rename the button's label by right-clicking it → Edit Text
To export a fixed named range or the whole used range every time instead of whatever's selected, replace `Set exportRange = Selection` with `Set exportRange = ActiveSheet.UsedRange` or `Set exportRange = Range("ReportArea")`.

Save the workbook as .xlsm (macro-enabled) before closing, or the macro won't be there next time you open it.

Want the full worked example?

Automate Your Reports: Power Query for Beginners

A complete, practical ebook covering the Folder connector, query folding, modular queries, and a full monthly-report walkthrough.

Get the eBook — ₹199