Macro to Create a PDF file from a Worksheet in Excel

These VBA macros save the active sheet, every sheet as its own PDF, or the whole workbook as one PDF, in the same folder as the workbook, ready to email.

Works inExcel 2010 and later, Windows and Mac
UndoNot possible. Save a copy first
Save asMacro-Enabled Workbook (.xlsm)

New to macros? See how to use the macros on this site: copy the code, paste it into a module with Alt + F11, and run it with Alt + F8.

Save the active sheet as PDF

Sub SaveSheetAsPDF()
    Dim f As String
    If ActiveWorkbook.Path = "" Then MsgBox "Save the workbook first.": Exit Sub
    f = ActiveWorkbook.Path & Application.PathSeparator & ActiveSheet.Name & ".pdf"
    ActiveSheet.ExportAsFixedFormat Type:=xlTypePDF, Filename:=f, OpenAfterPublish:=True
End Sub

The PDF is named after the sheet, such as Invoice.pdf, and opens when it’s ready. Change True to False to skip opening it.

Save each sheet as a separate PDF

Sub SaveEachSheetAsPDF()
    Dim ws As Worksheet, p As String
    p = ActiveWorkbook.Path & Application.PathSeparator
    For Each ws In Worksheets
        If ws.Visible = xlSheetVisible Then
            ws.ExportAsFixedFormat Type:=xlTypePDF, Filename:=p & ws.Name & ".pdf"
        End If
    Next ws
End Sub

Save the whole workbook as one PDF

Sub SaveWorkbookAsPDF()
    Dim f As String
    f = ActiveWorkbook.Path & Application.PathSeparator & "Full report.pdf"
    ActiveWorkbook.ExportAsFixedFormat Type:=xlTypePDF, Filename:=f
End Sub

Without a macro

  1. Go to File → Save As (or File → Export).
  2. Choose PDF as the file type.
  3. Click Options to choose the active sheet, the selection or the entire workbook, then Save.

Things to know

  • The PDF uses each sheet’s page setup. Set the print area, orientation and Fit to 1 page wide first, under Page Layout.
  • An existing PDF with the same name is replaced without warning.
  • To email the PDF automatically, combine this with the email through Outlook macro.

Related macros