Excel VBA to Export Selected Sheets to PDF

Exporting Selected Excel Sheets to PDF using VBA

Learn how to create a VBA macro in Excel to efficiently export specific worksheets or a range of sheets to a single PDF file, enhancing your reporting and document generation workflows.

Exporting data from Excel to PDF is a common requirement, especially when sharing reports or snapshots of your work. While Excel offers a built-in 'Save As PDF' option, it can be cumbersome when you need to export only a selection of sheets, or combine multiple sheets into a single PDF document. This article provides a comprehensive guide on how to leverage Excel VBA (Visual Basic for Applications) to automate this process, giving you greater control and efficiency.

Understanding the Core VBA Export Method

The primary VBA method for exporting to PDF is ExportAsFixedFormat. This method is part of the Workbook and Sheets objects, allowing you to specify what to export (the entire workbook, selected sheets, or a specific range) and how to export it (as a PDF or XPS document). Key parameters include the file format, output filename, quality, and whether to open the file after export.

Sub ExportSingleSheetToPDF()
    Dim ws As Worksheet
    Set ws = ThisWorkbook.Sheets("Sheet1") ' Change "Sheet1" to your desired sheet name
    
    Dim filePath As String
    filePath = ThisWorkbook.Path & "\SingleSheetExport.pdf"
    
    ws.ExportAsFixedFormat _
        Type:=xlTypePDF, _
        Filename:=filePath, _
        Quality:=xlQualityStandard, _
        IncludeDocProperties:=True, _
        IgnorePrintAreas:=False, _
        OpenAfterPublish:=True
        
    MsgBox "Sheet '" & ws.Name & "' exported to PDF successfully!", vbInformation
End Sub

VBA code to export a single specified sheet to a PDF file.

Exporting Multiple Selected Sheets to a Single PDF

The real power of VBA comes into play when you need to combine several sheets into one PDF document. This is achieved by selecting the desired sheets programmatically before calling the ExportAsFixedFormat method. The Sheets collection allows you to select multiple sheets by their names or indices. When multiple sheets are selected, Excel automatically combines their content into a single PDF, respecting their order in the selection.

Sub ExportMultipleSheetsToSinglePDF()
    Dim selectedSheets As Sheets
    Dim filePath As String
    
    ' Define the sheets you want to export
    ' You can use an array of sheet names or indices
    Set selectedSheets = ThisWorkbook.Sheets(Array("Sheet1", "ReportData", "Summary"))
    
    ' Define the output file path and name
    filePath = ThisWorkbook.Path & "\MultiSheetReport.pdf"
    
    On Error GoTo ErrorHandler
    
    ' Export the selected sheets as a single PDF
    selectedSheets.Select
    ActiveWorkbook.ExportAsFixedFormat _
        Type:=xlTypePDF, _
        Filename:=filePath, _
        Quality:=xlQualityStandard, _
        IncludeDocProperties:=True, _
        IgnorePrintAreas:=False, _
        OpenAfterPublish:=True
        
    MsgBox "Selected sheets exported to '" & filePath & "' successfully!", vbInformation
    Exit Sub
    
ErrorHandler:
    MsgBox "An error occurred: " & Err.Description, vbCritical
End Sub

VBA code to export multiple specified sheets into a single PDF document.

Dynamic Sheet Selection and User Interaction

For more flexible solutions, you might want to allow the user to select which sheets to export. This can be achieved by iterating through all sheets and checking if they are visible, or by presenting a user form (UserForm) with checkboxes for each sheet. Another approach is to let the user manually select sheets before running the macro, then export ActiveWindow.SelectedSheets.

Sub ExportActiveSelectedSheetsToPDF()
    Dim filePath As String
    
    ' Check if any sheets are selected
    If ActiveWindow.SelectedSheets.Count = 0 Then
        MsgBox "No sheets are currently selected. Please select one or more sheets.", vbExclamation
        Exit Sub
    End If
    
    ' Define the output file path and name
    filePath = ThisWorkbook.Path & "\UserSelectedSheets.pdf"
    
    On Error GoTo ErrorHandler
    
    ' Export the actively selected sheets as a single PDF
    ActiveWindow.SelectedSheets.ExportAsFixedFormat _
        Type:=xlTypePDF, _
        Filename:=filePath, _
        Quality:=xlQualityStandard, _
        IncludeDocProperties:=True, _
        IgnorePrintAreas:=False, _
        OpenAfterPublish:=True
        
    MsgBox "Actively selected sheets exported to '" & filePath & "' successfully!", vbInformation
    Exit Sub
    
ErrorHandler:
    MsgBox "An error occurred: " & Err.Description, vbCritical
End Sub

VBA code to export sheets that are currently selected by the user.

Workflow for exporting selected Excel sheets to PDF using VBA.

1. Open the VBA Editor

Press Alt + F11 in Excel to open the Visual Basic for Applications editor.

2. Insert a New Module

In the VBA editor, right-click on your workbook name in the Project Explorer (usually on the left), then select Insert > Module.

3. Paste the Code

Copy one of the provided VBA code examples (e.g., ExportMultipleSheetsToSinglePDF) and paste it into the newly created module.

4. Customize Sheet Names (if applicable)

Modify the Array("Sheet1", "ReportData", "Summary") line to include the exact names of the sheets you wish to export.

5. Run the Macro

Place your cursor anywhere within the pasted Sub routine, then press F5 or click the Run button (green triangle) in the VBA editor. Alternatively, go back to Excel, press Alt + F8, select your macro from the list, and click Run.

6. Verify the Output

A PDF file will be generated in the same directory as your Excel workbook (or opened directly if OpenAfterPublish is set to True). Check the PDF to ensure all selected sheets are included and formatted correctly.