If you need to send individual monthly reports, invoices, or department summaries to clients or executives, manually saving each sheet as a separate PDF file takes unnecessary time and effort.

In this guide, you will learn how to run a simple Excel VBA macro that automatically exports every worksheet in your workbook (or selected sheets) into clean, individual PDF files with just one click.

---

Why Automate PDF Exports with VBA?

Automating your PDF generation workflow eliminates daily administrative bottlenecks:

• Instant Batch Saving: Export dozens of sheets in seconds instead of repeating File ➔ Export ➔ PDF manually.
• Standardized Naming: Automatically names each PDF file using the exact worksheet title.
• Same-Folder Organization: Automatically saves output PDF files directly into the same folder as your Excel workbook.

---

Step 1: Open the VBA Editor Window

1. Open your Excel workbook containing the sheets you want to export.
2. Press Alt + F11 (Option + F11 on Mac) to launch the Visual Basic Editor.
3. In the top menu, click Insert ➔ Module to create a new blank code window.

---

Step 2: Copy and Paste the PDF Export VBA Code

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

Sub ExportSheetsToPDF()
    Dim ws As Worksheet
    Dim folderPath As String
    Dim pdfFilePath As String
    Dim exportedCount As Long
    
    ' 1. Get the current folder path of the workbook
    folderPath = Application.ActiveWorkbook.Path
    
    ' Ensure file is saved before running
    If folderPath = "" Then
        MsgBox "Please save your Excel workbook first before running this macro!", vbExclamation, "File Not Saved"
        Exit Sub
    End If
    
    ' Add trailing slash to path
    folderPath = folderPath & "\"
    
    ' 2. Pause screen updating for speed
    Application.ScreenUpdating = False
    exportedCount = 0
    
    ' 3. Loop through each visible worksheet
    For Each ws In ActiveWorkbook.Worksheets
        If ws.Visible = xlSheetVisible Then
            ' Define output path (FolderPath + SheetName + .pdf)
            pdfFilePath = folderPath & ws.Name & ".pdf"
            
            ' Export sheet as PDF
            ws.ExportAsFixedFormat _
                Type:=xlTypePDF, _
                Filename:=pdfFilePath, _
                Quality:=xlQualityStandard, _
                IncludeDocProperties:=True, _
                IgnorePrintAreas:=False, _
                OpenAfterPublish:=False
                
            exportedCount = exportedCount + 1
        End If
    Next ws
    
    ' 4. Re-enable screen updating
    Application.ScreenUpdating = True
    
    ' 5. Completion notice
    MsgBox exportedCount & " sheets successfully exported as PDFs into your folder!", vbInformation, "Excel Owl Automation"
End Sub

---

Step 3: Run the Macro Using Alt + F8

Run your new PDF generator directly from your Excel sheet whenever needed:

1. Switch back to your main Excel window.
2. Press Alt + F8 (or Option + F8 on Mac) to open the Macro dialog box.
3. Select ExportSheetsToPDF from the list.
4. Click Run.

💡 Pro Tip: Hidden Sheets Protection
This macro automatically skips hidden worksheets, ensuring only active and visible sheets are exported to PDF!

---

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

To preserve your macro script for ongoing use, make sure to save your file correctly:

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

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