How to Automatically Create Multiple PDFs from an Excel List using VBA
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.

- 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.
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.
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.
Press Alt + F11 in Excel to open the VBA Editor, and locate the module containing your macro.
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.
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.
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.
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. Open your file in WPS Spreadsheet: Launch WPS Office and open your .xlsm file containing the data validation list and dashboard.
- 2. Enable Macros: Navigate to the 'Developer' tab on the ribbon and ensure macros are enabled for the current workbook.
- 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.

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.




