Excel Owl

Tag: batch-export

  • How to Merge Multiple Excel Sheets into a Single PDF File Using VBA

    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.

  • How to Export Excel Sheets as Separate PDFs Using VBA (One Click)

    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.