VBA & Macro Problems

How to Merge Multiple Worksheets into One PDF with Excel VBA

Maira Mehtab Oct 13, 2026 869 views

Question details

The user wants to export multiple Excel worksheets into a single PDF document using VBA instead of creating separate PDF files for each sheet.

How to Merge Multiple Worksheets into One PDF Using Excel VBA

Product: Microsoft Excel
Device & OS: not provided
Scenario: Exporting multiple worksheets to PDF automatically using an Excel VBA script.
Observed behavior: Setting the OpenAfterPublish attribute to True while exporting sheets individually opens dozens of separate Adobe Reader windows, which causes system clutter and exceeds file limits.

Before you start

Ensure you have saved your current Excel workbook to prevent data loss before running or modifying new VBA macros, and verify that your system has a default PDF viewer installed.

Solution 1Recommended

Group Worksheets and Export as a Single PDF

Instead of looping through and exporting each sheet individually, select the necessary sheets in VBA and save them as one combined PDF file.

By grouping the target worksheets either into an array or copying them into a temporary new workbook, Excel treats them as a single print job. This ensures only one PDF is generated and only one PDF reader window opens.

Select the target worksheets

In your VBA code, identify the worksheets you want to export. You can select multiple sheets simultaneously using an array, for example: Sheets(Array("Sheet1", "Sheet2", "Sheet3")).Select.

Apply the PDF export method

Use the ExportAsFixedFormat method on the ActiveSheet. This will apply to all grouped sheets. The code should look like: ActiveSheet.ExportAsFixedFormat Type:=xlTypePDF, Filename:="C:\YourPath\Combined.pdf".

Adjust publish settings

Ensure that the OpenAfterPublish parameter is set to True (or False if you prefer). Because the sheets are processed together, only one Adobe Reader window will launch, displaying the combined file.

Group Worksheets and Export as a Single PDF

Avoid System Resource Limits: This method effectively prevents your PDF reader and Windows OS from hitting file limitation constraints by generating a single, cleanly merged document.

Solution 2

Export Individually and Combine Later with a PDF Tool

If your workflow requires individual sheet processing for other reasons, you can suppress the auto-open behavior and merge the PDFs post-export.

Advanced PDF Export Tools

Easily Export and Merge Spreadsheets to PDF with WPS Office

WPS Office provides robust built-in tools to manage multiple worksheets and export them as a single, beautifully formatted PDF document seamlessly, offering a highly compatible environment for your VBA scripts.

1. Open your spreadsheet: Launch WPS Spreadsheet and open the workbook containing the multiple sheets you want to merge.
2. Access the PDF export tool: Go to the Menu or Tools tab on the ribbon interface and click on the 'Export to PDF' button.
3. Select sheets to export: In the export dialog box, choose the option to export the 'Entire Workbook' or manually check specific sheets to include in the merged PDF.
4. Save the file: Choose your destination folder and click 'Export' to generate one combined PDF document instantly.

Built-in PDF converter to easily export multiple worksheets as one combined file without writing complex code.High compatibility with Microsoft Excel (.xlsx) formats and standard VBA macros.Lightweight application that prevents system lag when handling dozens of sheets simultaneously.

microsoft office alternative - wps office

Frequently Asked Questions

Why does my Excel VBA macro crash when exporting multiple PDFs?

When exporting many sheets individually with OpenAfterPublish:=True in VBA, it forces the system to open dozens of PDF reader windows simultaneously. This quickly exhausts system memory or exceeds Windows file limitations, causing Excel or the PDF reader to crash.

How do I select multiple specific sheets in VBA for PDF export?

You can group specific worksheets in VBA by passing an array of sheet names to the Sheets object, like this: Sheets(Array("Sheet1", "Sheet3", "Sheet5")).Select. Once selected, apply the ExportAsFixedFormat method to the ActiveSheet.

Can I merge existing PDF files using Excel VBA?

Excel VBA cannot natively merge existing PDF files on its own. You would need to reference a third-party library (like Adobe Acrobat API or PDFCreator) within your VBA code, or use a dedicated PDF merger tool after exporting.

How can I stop PDFs from opening automatically after exporting via VBA?

In your VBA ExportAsFixedFormat method, locate the OpenAfterPublish parameter and set it to False. This setting will generate the PDF silently in the background without launching your default PDF viewer.

Excel & Data Problems