VBA & Macro Problems

How to Automatically Create Multiple PDFs from an Excel List using VBA

John WilsonJohn Wilson Nov 4, 2026 870 views

Question details

The user wants to automate the process of exporting multiple PDFs in Excel by looping through a data-validation list, updating a dashboard cell, and saving each view as a separate PDF file.

Automatically Create Multiple PDFs from an Excel List with VBA
Product
Excel
Device & OS
not provided
Scenario
Generating individual PDF reports or dashboards from a dynamic Excel list using a macro.
Observed behavior
The VBA code throws a Runtime Error 1004 when attempting to export the PDFs, preventing the automated file creation.
Before you start

Ensure that the destination folder path (e.g., local drive or network share) actually exists and that you have write permissions to save files in that specific location.

Solution 1Recommended

Implement the VBA Loop and Fix Path Errors

Use a VBA loop to iterate through the data validation list, update the target cell, and export each iteration while preventing Runtime Error 1004.

The 'Runtime Error 1004' during PDF export is typically caused by an invalid file path, a missing destination folder, or illegal characters in the file name. To automate multiple PDF creations, you need to loop through the source list and ensure the path string is properly constructed.

1
Open the VBA Editor

Press Alt + F11 in Excel to open the VBA Editor, and locate the module containing your macro.

2
Check and Update the File Path

Verify that your target path (e.g., 'J:\All Staff\Student Data\2020 2021\Maths Sheets\') exists. If it is a mapped network drive, ensure it is actively connected in Windows File Explorer. Update the path string in your code if the folder structure has changed.

3
Add a Loop Structure

Wrap your ExportAsFixedFormat code in a For Each loop. Reference the source range of your data validation list (e.g., Range("SourceList")), assign the current value to cell K7, and calculate the sheet before exporting.

4
Run the Batch Export

Save the workbook as a Macro-Enabled Workbook (.xlsm). Return to your spreadsheet, go to the Developer tab, click Macros, select your script, and click Run to generate the PDFs.

Check Illegal Characters: Make sure the 'ID' value from cell K7 doesn't contain characters like <, >, ?, [, ], :, or *, which Windows blocks in file names and will instantly trigger Error 1004.
Advanced Spreadsheets Solution

Automate PDF Generation with WPS Spreadsheet

WPS Spreadsheet provides excellent support for VBA macros, allowing you to seamlessly loop through data validation lists and automatically generate PDFs. It is fully compatible with standard Excel macro scripts.

  1. 1. Open your file in WPS Spreadsheet: Launch WPS Office and open your .xlsm file containing the data validation list and dashboard.
  2. 2. Enable Macros: Navigate to the 'Developer' tab on the ribbon and ensure macros are enabled for the current workbook.
  3. 3. Run the VBA code: Click on 'Macros' in the Developer tab, select your PDF creation macro, and click 'Run' to generate your PDFs automatically.
Fully compatible with Microsoft Excel VBA code (.xlsm files)Built-in, seamless PDF conversion and export toolsLightweight application that handles complex macros efficiently
microsoft office alternative - wps office

Frequently Asked Questions

What causes Runtime Error 1004 when exporting Excel to PDF via VBA?

Runtime error 1004 usually occurs because the specified file path does not exist, a network drive is disconnected, you lack write permissions to the destination folder, or the generated filename contains invalid characters.

How can I loop through a data validation list in VBA?

You can loop through a validation list by referencing the source range of the validation rule (e.g., passing the named range into a For Each loop), iterating through the values, and assigning each value to your target dashboard cell.

How do I sanitize a filename in Excel VBA?

You can use the Replace function in VBA to remove or swap invalid characters like backslashes, forward slashes, colons, asterisks, question marks, quotation marks, and angle brackets before appending the ID string to the file path.

Does ExportAsFixedFormat work on mapped network drives?

Yes, but the network drive must be mapped, authenticated, and accessible at the exact time the macro runs. If the network connection drops or the drive letter changes, the macro will fail.