Blog

Saving Excel files as PDF is a common need for users, especially when sending financial reports or official documents that should not be edited. In this post, you’ll learn simple to advanced VBA coding solutions for converting Excel to PDF; from converting a simple sheet to printing multiple separate pages and managing save paths.

Common Methods for Saving PDF in Excel (Without VBA)

Excel has built-in capability to save files in PDF format: from the File → Save As → PDF or File → Export → Create PDF/XPS menu, you can convert a sheet or the entire workbook to PDF. However, this method is manual and time-consuming for frequent repetitions, daily reports, or multi-part files. This is where VBA with its automation saves time.

Simple VBA Code to Save Active Sheet as PDF

This code saves the active sheet with the same name as the Excel file in the same path:

Sub SaveActiveSheetAsPDF()
    Dim FilePath As String
    FilePath = ThisWorkbook.Path & "\" & "Active_Sheet.pdf"
    ActiveSheet.ExportAsFixedFormat Type:=xlTypePDF, Filename:=FilePath
    MsgBox "File saved successfully: " & FilePath
End Sub

• ThisWorkbook.Path: Path of the current Excel file
• ActiveSheet: Only the active sheet
• ExportAsFixedFormat: Main method for PDF output

Select Multiple Sheets and Convert Them to a Single PDF File

To save multiple sheets in one PDF file:

Sub SaveMultipleSheetsAsPDF()
    Dim SheetsToPrint As Variant
    SheetsToPrint = Array("Financial Report", "Credit note invoice") 'Sheet names
    Sheets(SheetsToPrint).Select
    ActiveSheet.ExportAsFixedFormat Type:=xlTypePDF, _
        Filename:=ThisWorkbook.Path & "\Multiple_Sheets.pdf"
    Sheets(1).Select 'Return to normal state
End Sub

Save and Convert Each Sheet to a Separate PDF File

Sub SaveAllSheetsAsSeparatePDFs()
    Dim ws As Worksheet
    Dim Filename As String
    
    For Each ws In ThisWorkbook.Worksheets
        Filename = ThisWorkbook.Path & "\" & ws.Name & ".pdf"
        ws.ExportAsFixedFormat Type:=xlTypePDF, Filename:=Filename
    Next ws

    MsgBox "Completed successfully."
End Sub

Important Note:

If the sheet name contains invalid filename characters such as "\", "/", ":", "*", "?", """", "<", ">", "|", the macro will not complete successfully and will generate runtime error -2147024773(8007007b) and the macro will stop.

To fix the above problem, you can use the following helper function and clean the Excel sheet name from invalid characters before using it as a filename. This function replaces invalid characters (the InvalidChars array) in the string s (function input) with underscore character (_).

Private Function CleanFileName(s As String) As String
    Dim InvalidChars As Variant
    Dim i As Long
    InvalidChars = Array("\", "/", ":", "*", "?", """", "<", ">", "|")
    For i = LBound(InvalidChars) To UBound(InvalidChars)
        s = Replace(s, InvalidChars(i), "_")
    Next i
    s = Trim(s)
    If Len(s) = 0 Then s = "Sheet"
    CleanFileName = s
End Function

Sub SaveAllSheetsAsSeparatePDFs()
    Dim ws As Worksheet
    Dim Filename As String
    
    For Each ws In ThisWorkbook.Worksheets
        Filename = ThisWorkbook.Path & "\" & CleanFileName(ws.Name) & ".pdf"
        ws.ExportAsFixedFormat Type:=xlTypePDF, Filename:=Filename
    Next ws

    MsgBox "Completed successfully."
End Sub

Dynamically Specify Filename and Path

Saving file with today’s date:

Sub SaveWithDate()
    Dim FileName As String
    FileName = "Report_" & Format(Date, "yyyy-mm-dd") & ".pdf"
    ThisWorkbook.ExportAsFixedFormat Type:=xlTypePDF, _
        Filename:=ThisWorkbook.Path & "\" & FileName
End Sub

Print and Convert to PDF Using File Dialog

User can select desired path:

Sub SavePDFWithDialog()
    Dim FilePath As Variant
    FilePath = Application.GetSaveAsFilename( _
                InitialFileName:="Report.pdf", _
                FileFilter:="PDF Files (*.pdf), *.pdf")
    If FilePath <> False Then
        ActiveSheet.ExportAsFixedFormat Type:=xlTypePDF, Filename:=FilePath
        MsgBox "File saved at selected location."
    End If
End Sub

Print One Sheet to Multiple Files with Page Break

Each page is saved in a separate PDF file:

Sub ExportByPageBreaks()
    Dim ws As Worksheet, rng As Range
    Dim pb As HPageBreak, startRow As Long, endRow As Long
    Dim i As Long, FilePath As String
    
    Set ws = ActiveSheet
    FilePath = ThisWorkbook.Path & "\Page_"
    startRow = 1
    i = 1
    
    For Each pb In ws.HPageBreaks
        endRow = pb.Location.Row - 1
        Set rng = ws.Rows(startRow & ":" & endRow)
        rng.ExportAsFixedFormat Type:=xlTypePDF, _
            Filename:=FilePath & i & ".pdf"
        startRow = pb.Location.Row
        i = i + 1
    Next pb
    
    'Last page
    Set rng = ws.Rows(startRow & ":" & ws.UsedRange.Rows.Count)
    rng.ExportAsFixedFormat Type:=xlTypePDF, _
        Filename:=FilePath & i & ".pdf"
        
    MsgBox "Saved " & i & " pages completed."
End Sub

Error Handling

To prevent program stop in case of problems:

Sub SavePDF_WithErrorHandling()
    On Error GoTo ErrHandler
    ActiveSheet.ExportAsFixedFormat Type:=xlTypePDF, Filename:=ThisWorkbook.Path & "\Test.pdf"
    MsgBox "Completed successfully."
    Exit Sub
ErrHandler:
    MsgBox "Error: " & Err.Description
End Sub

Creating and Running Macros in Excel

1. Go to the Developer menu and select Visual Basic. More information about how to enable the Developer menu can be found on the How to enable Developer tab in Excel? page.
2. From the Insert menu, select Module to create a new module. You can see a visual tutorial on how to add a standard module in VBA on the Module in VBA page.
3. Double-click the Module1 icon in the Project Explorer window to open the code window for this module. (To learn more about the VBA Code Editor or VBE, you can visit this page)
4. Copy the following code into the Module1 code window.

VBA Codes for Excel to PDF Conversion

'=== 1. Save the active sheet as a PDF ===
Sub SaveActiveSheetAsPDF()
    Dim FilePath As String
    FilePath = ThisWorkbook.Path & "\" & "Active_Sheet.pdf"
    ActiveSheet.ExportAsFixedFormat Type:=xlTypePDF, Filename:=FilePath
    MsgBox "File saved successfully: " & FilePath
End Sub

'=== 2. Save multiple specific sheets as one PDF ===
Sub SaveMultipleSheetsAsPDF()
    Dim SheetsToPrint As Variant
    SheetsToPrint = Array("Financial Report", "Credit note invoice") 'Sheet names
    Sheets(SheetsToPrint).Select
    ActiveSheet.ExportAsFixedFormat Type:=xlTypePDF, _
        Filename:=ThisWorkbook.Path & "\Multiple_Sheets.pdf"
    Sheets(1).Select 'Return to normal state
End Sub

'=== 3. Helper function to clean invalid file name characters ===
Private Function CleanFileName(s As String) As String
    Dim InvalidChars As Variant
    Dim i As Long
    InvalidChars = Array("\", "/", ":", "*", "?", """", "<", ">", "|")
    For i = LBound(InvalidChars) To UBound(InvalidChars)
        s = Replace(s, InvalidChars(i), "_")
    Next i
    s = Trim(s)
    If Len(s) = 0 Then s = "Sheet"
    CleanFileName = s
End Function

'=== 4. Save all sheets as separate PDF files ===
Sub SaveAllSheetsAsSeparatePDFs()
    Dim ws As Worksheet
    Dim Filename As String
    
    For Each ws In ThisWorkbook.Worksheets
        Filename = ThisWorkbook.Path & "\" & CleanFileName(ws.Name) & ".pdf"
        ws.ExportAsFixedFormat Type:=xlTypePDF, Filename:=Filename
    Next ws

    MsgBox "Completed successfully."
End Sub

'=== 5. Save the entire workbook with current date in the file name ===
Sub SaveWithDate()
    Dim FileName As String
    FileName = "Report_" & Format(Date, "yyyy-mm-dd") & ".pdf"
    ThisWorkbook.ExportAsFixedFormat Type:=xlTypePDF, _
        Filename:=ThisWorkbook.Path & "\" & FileName
End Sub

'=== 6. Save using a Save As dialog (user selects location) ===
Sub SavePDFWithDialog()
    Dim FilePath As Variant
    FilePath = Application.GetSaveAsFilename( _
                InitialFileName:="Report.pdf", _
                FileFilter:="PDF Files (*.pdf), *.pdf")
    If FilePath <> False Then
        ActiveSheet.ExportAsFixedFormat Type:=xlTypePDF, Filename:=FilePath
        MsgBox "File saved at selected location."
    End If
End Sub

'=== 7. Save each page (based on horizontal page breaks) as a separate PDF ===
Sub ExportByPageBreaks()
    Dim ws As Worksheet, rng As Range
    Dim pb As HPageBreak, startRow As Long, endRow As Long
    Dim i As Long, FilePath As String
    
    Set ws = ActiveSheet
    FilePath = ThisWorkbook.Path & "\Page_"
    startRow = 1
    i = 1
    
    For Each pb In ws.HPageBreaks
        endRow = pb.Location.Row - 1
        Set rng = ws.Rows(startRow & ":" & endRow)
        rng.ExportAsFixedFormat Type:=xlTypePDF, _
            Filename:=FilePath & i & ".pdf"
        startRow = pb.Location.Row
        i = i + 1
    Next pb
    
    'Last page
    Set rng = ws.Rows(startRow & ":" & ws.UsedRange.Rows.Count)
    rng.ExportAsFixedFormat Type:=xlTypePDF, _
        Filename:=FilePath & i & ".pdf"
        
    MsgBox "Saved " & i & " pages completed."
End Sub

'=== 8. Save with basic error handling ===
Sub SavePDF_WithErrorHandling()
    On Error GoTo ErrHandler
    ActiveSheet.ExportAsFixedFormat Type:=xlTypePDF, Filename:=ThisWorkbook.Path & "\Test.pdf"
    MsgBox "Completed successfully."
    Exit Sub
ErrHandler:
    MsgBox "Error: " & Err.Description
End Sub

Video Tutorial: Convert Excel to PDF Using VBA

Real-World Applications (Practical Examples)

• Automatically sending monthly accounting reports to organization managers.
• Saving and archiving government project status reports in PDF format.
• Quickly preparing sales invoices for customers without manual settings.

Summary and Recommendations

• The ExportAsFixedFormat method is the core of Excel to PDF conversion.
• By combining these codes, you can build financial automation or automatic reporting systems.
• For user convenience, place a button (Form Control Button) on the sheet and connect the above macros to it.