How to Export Selected Excel Worksheets to One PDF via VBA
Question details
The user wants to export a customized sequence of Excel worksheets into a single PDF using a VBA macro, omitting the sheet with the trigger button and allowing specific sheets to appear multiple times.

- Product
- Excel
- Device & OS
- not provided
- Scenario
- Automating the generation of a consolidated PDF report from specific Excel sheets while omitting the control interface sheet.
- Observed behavior
- A custom sequence of worksheets is compiled into one PDF document, excluding the control sheet, with certain sheets successfully duplicated in the output sequence.
Ensure you have the Developer tab enabled in your ribbon and have saved your workbook as a Macro-Enabled Workbook (.xlsm) to preserve your VBA code.
Use a VBA Array to Select and Export Worksheets
Define a specific array of worksheet names to control exactly which sheets are exported to the PDF, allowing duplicates and excluding the button sheet.
By creating an array of worksheet names, you can explicitly dictate the order and frequency of sheets in the final PDF. Any sheet not included in the array, such as the sheet containing your macro button, will automatically be excluded from the print selection.
Press Alt + F11 in your workbook to open the Microsoft Visual Basic for Applications editor, then click Insert > Module from the top menu.
Create a Variant variable and assign your sheet names to it using the Array function. For example: Dim SheetArray As Variant followed by SheetArray = Array("COWBOYS FOR FRIENDS", "COWBOYS FOR FRIENDS", "EAGLES FOR FRIENDS"). Simply omit the name of the sheet housing your macro button.
Use the command Sheets(SheetArray).Select to group-select exactly the worksheets you just defined in your array.
Call the export function on the active selection using ActiveSheet.ExportAsFixedFormat Type:=xlTypePDF, Filename:="C:\YourPath\Output.pdf".

Experience Powerful Spreadsheet Management with WPS Office
Handling complex VBA macros and PDF exports is seamless with WPS Office. As a lightweight, highly compatible alternative to Microsoft Office, WPS Spreadsheet allows you to execute macros and manage your data with familiar tools—all for free.
- 1. Download WPS Office: Visit the official WPS website and download the free installation package for your operating system.
- 2. Install the Suite: Run the installer and follow the quick on-screen prompts to set up WPS Office on your device.
- 3. Open Your Spreadsheet: Launch WPS Spreadsheet and open your .xlsm file to seamlessly view your data, run macros, and export to PDF.

Frequently Asked Questions
Why is my macro button showing up in the exported PDF?
This happens if the macro button is placed on a worksheet that is included in your export array, or if its properties are set to "Print object". To prevent this, place the button on a dedicated control sheet that is strictly excluded from your VBA array.
Can I export multiple copies of the same worksheet in one PDF?
Yes, by using a VBA array to define the export sequence, you can list the same worksheet name multiple times (e.g., Array("Sheet1", "Sheet1")). The macro will export the sheet in the exact order and frequency you specify.
What VBA method is used to save selected sheets as a PDF?
The ExportAsFixedFormat method handles this. After selecting the desired sheets via your array, apply ActiveSheet.ExportAsFixedFormat Type:=xlTypePDF along with your desired file path to generate the PDF document.
Will this VBA macro work if the worksheet names change?
If the worksheet names are hardcoded in the VBA array as strings, the macro will fail if the actual sheet tabs are renamed. You must update the strings in your VBA array to match any newly renamed worksheets.




