When preparing end-of-month financial packets, client presentations, or audit files, sending dozens of separate PDF files can look disorganized. Combining multiple worksheets into a single, seamless PDF document is much more professional.

In this guide, you will learn how to use a simple Excel VBA macro that automatically selects multiple worksheets (or all visible sheets) and exports them into a single, multi-page PDF document with just one click.

---

Why Combine Sheets into One PDF via VBA?

Merging multiple tabs into one PDF manually requires repeatedly rearranging print settings or using external PDF merging tools. Automating this workflow provides:

• Single-File Output: Combines all specified report tabs into one clean PDF document.
• Automatic Page Ordering: Keeps your sheets in the exact order they appear in your Excel workbook.
• Third-Party Tool Elimination: No need to upload confidential spreadsheets to free online PDF mergers.

---

Step 1: Open the VBA Editor Window

1. Open your Excel workbook containing the sheets you wish to combine into a PDF.
2. Press Alt + F11 (Option + F11 on Mac) to open the Visual Basic Editor.
3. In the top menu, click Insert ➔ Module to open a clean code window.

---

Step 2: Copy and Paste the Combined PDF VBA Code

Copy the code block below and paste it directly into your blank module window:

Sub ExportAllSheetsToSinglePDF()
    Dim folderPath As String
    Dim pdfFileName As String
    Dim fullPdfPath As String
    Dim ws As Worksheet
    Dim sheetArray() As String
    Dim count As Long
    
    ' 1. Get current workbook folder path
    folderPath = Application.ActiveWorkbook.Path
    
    If folderPath = "" Then
        MsgBox "Please save your Excel workbook first before running this macro!", vbExclamation, "File Not Saved"
        Exit Sub
    End If
    
    ' 2. Define output PDF file name
    pdfFileName = "Combined_Report_" & Format(Date, "YYYYMMDD") & ".pdf"
    fullPdfPath = folderPath & "\" & pdfFileName
    
    ' 3. Collect all visible worksheets into an array
    count = 0
    For Each ws In ActiveWorkbook.Worksheets
        If ws.Visible = xlSheetVisible Then
            ReDim Preserve sheetArray(count)
            sheetArray(count) = ws.Name
            count = count + 1
        End If
    Next ws
    
    If count = 0 Then
        MsgBox "No visible sheets found to export!", vbExclamation, "Excel Owl Automation"
        Exit Sub
    End If
    
    ' 4. Select visible sheets and export as a single PDF
    Application.ScreenUpdating = False
    
    Worksheets(sheetArray).Select
    ActiveSheet.ExportAsFixedFormat _
        Type:=xlTypePDF, _
        Filename:=fullPdfPath, _
        Quality:=xlQualityStandard, _
        IncludeDocProperties:=True, _
        IgnorePrintAreas:=False, _
        OpenAfterPublish:=True
        
    ' Reselect the first sheet to un-group worksheets
    Worksheets(sheetArray(0)).Select
    
    Application.ScreenUpdating = True
    
    MsgBox "All visible sheets exported successfully to:" & vbCrLf & pdfFileName, vbInformation, "Excel Owl Automation"
End Sub

---

Step 3: Run the Macro Using Alt + F8

Execute your single PDF generator directly from Excel:

1. Switch back to your main Excel workbook window.
2. Press Alt + F8 (or Option + F8 on Mac) to bring up the Macro dialog box.
3. Select ExportAllSheetsToSinglePDF from the list.
4. Click Run. The combined PDF will open automatically upon completion!

💡 Pro Tip: Exclude Specific Tabs
If you want to skip certain administrative or lookup tabs, simply right-click those sheet tabs in Excel and select Hide. The macro automatically ignores hidden sheets!

---

Step 4: Save as Macro-Enabled Workbook (.xlsm)

To retain your macro code for future reporting runs, make sure to save in the correct format:

1. Click File ➔ Save As (or press F12).
2. In the Save as type drop-down, select Excel Macro-Enabled Workbook (*.xlsm).
3. Click Save.

⚠️ Important Note: Standard .xlsx files cannot store macros. Saving as .xlsx will permanently erase your VBA script.