Export a sheet to PDF with VBA

First principles. An analyst's reporting ritual is almost always the same three verbs. You pull the current numbers, format them into something presentable, then export a fixed, un-editable copy (a PDF) to a known place with a dated name. By hand that's fifteen minutes of clicking that you'll repeat fifty times a year. Twice a year you'll get it wrong in some small way you don't spot. In code it's a button. One method drives the export half, Worksheet.ExportAsFixedFormat. The craft is all in the wrapper around it: fast mode on, output folder verified, filename dated and idempotent, and state restored no matter what.

ExportAsFixedFormat publishes a sheet (or a selection of sheets, or the whole workbook) to PDF or XPS. These are the arguments you'll actually use:

object.ExportAsFixedFormat(Type, Filename, Quality, IncludeDocProperties, _
                           IgnorePrintAreas, From, To, OpenAfterPublish)

• Type:=xlTypePDF: PDF. The other option, xlTypeXPS, you'll never use.
• Filename, the full output path. Most failures happen here. If the folder doesn't exist you get run-time error 1004, and if the file is already open in a PDF viewer you get a sharing violation.
• Quality:=xlQualityStandard, standard vs minimum. Standard is right for anything a human reads.
• IncludeDocProperties:=True: embeds title/author metadata.
• IgnorePrintAreas:=False respects the print areas you set, so each sheet paginates the way you designed it and doesn't sprawl.
• OpenAfterPublish opens the PDF when it's done. Use True while you're developing and False in production, so an unattended run doesn't spawn a viewer.

How do you export several sheets to one PDF report pack?

One detail trips up everyone. To get several sheets into one PDF, you select them as a group first, then call ExportAsFixedFormat on the active sheet, and Excel exports the whole selection. You've been avoiding .Select since VBA I, and here it's the actual mechanism. Grouping sheets for export is the one place it isn't an anti-pattern. Here's the production build:

Public Sub BuildReportPack()
    ' One-click: verify output folder, export a dated multi-sheet PDF pack, restore state.
    Const OUT_DIR As String = "C:\Reports"          ' change in ONE place
    Dim packSheets As Variant
    packSheets = Array("Cover", "Summary", "Pune", "Mumbai", "Delhi")  ' 5 pages, in order

    ' 1) Capture prior application state BEFORE touching anything.
    Dim prevScreen As Boolean, prevEvents As Boolean
    Dim prevCalc As XlCalculation
    prevScreen = Application.ScreenUpdating
    prevEvents = Application.EnableEvents
    prevCalc = Application.Calculation

    On Error GoTo Cleanup                            ' any failure jumps to restore

    Application.ScreenUpdating = False
    Application.EnableEvents = False
    Application.Calculation = xlCalculationManual
    Application.StatusBar = "Building report pack ..."

    ' 2) Idempotent, dated filename. Ensure the output folder exists first.
    If Len(Dir(OUT_DIR, vbDirectory)) = 0 Then MkDir OUT_DIR   ' one level; nest = loop MkDir
    Dim outFile As String
    outFile = OUT_DIR & "\MonthlyPack_" & Format$(Date, "yyyy-mm-dd") & ".pdf"

    ' 3) Select the pack sheets as a group, export the selection as ONE pdf.
    ThisWorkbook.Worksheets(packSheets).Select
    ActiveSheet.ExportAsFixedFormat _
        Type:=xlTypePDF, _
        Filename:=outFile, _
        Quality:=xlQualityStandard, _
        IncludeDocProperties:=True, _
        IgnorePrintAreas:=False, _
        OpenAfterPublish:=False

    ThisWorkbook.Worksheets(packSheets(0)).Select    ' collapse the multi-sheet selection

Cleanup:
    Dim errNum As Long, errDesc As String
    errNum = Err.Number: errDesc = Err.Description    ' grab before anything else can reset Err
    Application.ScreenUpdating = prevScreen
    Application.EnableEvents = prevEvents
    Application.Calculation = prevCalc
    Application.StatusBar = False                     ' hand the status bar back to Excel
    If errNum = 0 Then
        MsgBox "Report pack saved:" & vbCrLf & outFile, vbInformation
    Else
        MsgBox "Report build failed: " & errDesc & " (error " & errNum & ")", vbExclamation
    End If
End Sub

So what turns this from a demo into production code? Start with state. It's captured into prev* variables before the On Error line, so the Cleanup label can put back the values the user had. Blindly forcing ScreenUpdating = True would be wrong if the caller had turned it off on purpose. Next, the Cleanup label is reached on both the happy path, where execution simply falls into it, and on any error, where the On Error GoTo jumps there. There's only one exit, and it always restores. Last, the filename carries the date. Today's run overwrites today's file. Run it twice and you still get one file (that's idempotent), but it never clobbers yesterday's.