VBA & Macro Problems

How to Save Multiple Excel Worksheets as PDFs Using Cell Values in VBA

Aamir Naveed Akram Oct 9, 2026 870 views

Question details

The user needs an Excel VBA macro to loop through multiple scorecard worksheets and save each one as a separate PDF, using the value in cell M5 of the respective worksheet as the filename.

How to Save Multiple Excel Worksheets as PDFs Using Cell Values in VBA

Product: Excel
Device & OS: not provided
Scenario: Automating the export of multiple newly generated scorecard worksheets to PDF format.
Observed behavior: The current macro saves only one worksheet as a PDF or saves the files in the wrong default location because it relies on ActiveSheet instead of iterating through all required worksheets.

Before you start

Ensure that macros are enabled in your Excel workbook and verify that your intended destination folder path already exists on your computer before running the VBA code.

Solution 1Recommended

Use a VBA Loop and Define the Destination Path

Update your VBA macro to explicitly iterate through each worksheet using a Worksheet object variable and construct the full save path using a dedicated folder string.

By utilizing a 'For Each' loop and targeting the specific worksheet object (ws) rather than the ActiveSheet, the macro is forced to process every scorecard in the workbook. Additionally, appending the folder directory to the cell value ensures the file is routed exactly where you need it.

Open the VBA Editor

Press Alt + F11 to open the Visual Basic for Applications (VBA) editor in Excel.

Declare Variables

Set up your variables by typing: Dim ws As Worksheet, s As String, and saveLocation As String.

Define the Save Location

Assign your target folder path to the variable, making sure it ends with a backslash. Example: saveLocation = "C:\Users\Username\Downloads\"

Create the Loop

Write a loop to iterate through all sheets: For Each ws In ThisWorkbook.Worksheets

Extract the Cell Value and Export

Inside the loop, retrieve the filename from cell M5 and export the sheet using: s = ws.Range("M5").Value followed by ws.ExportAsFixedFormat Type:=xlTypePDF, Filename:=saveLocation & s & ".pdf", Quality:=xlQualityStandard, IncludeDocProperties:=True, IgnorePrintAreas:=False, OpenAfterPublish:=False.

Use a VBA Loop and Define the Destination Path

Avoid Using ActiveSheet: Using a specific worksheet variable (ws) instead of ActiveSheet guarantees that the macro processes every sheet in the loop rather than overwriting the currently viewed one.

WPS Spreadsheet Macro Support

Run VBA Macros to Export PDFs Seamlessly with WPS Spreadsheets

WPS Office offers robust support for VBA and Excel macros, allowing you to easily run automated scripts like exporting multiple sheets to PDF. Enjoy high compatibility with Excel files and a smooth automation experience.

1. Open Your Workbook in WPS: Launch WPS Office and open your macro-enabled spreadsheet containing the scorecards.
2. Access the Developer Tools: Navigate to the Developer tab on the ribbon to access the VBA and Macro environment.
3. Run the Export Macro: Open the VBA Editor (Alt + F11), paste your updated PDF export code with the worksheet loop, and run it to instantly generate your formatted PDFs.

Fully compatible with Microsoft Excel .xlsm formats and VBA scripts.Built-in robust PDF converter without needing third-party add-ins.Lightweight software that executes heavy macro loops rapidly.Familiar user interface requiring zero learning curve.

microsoft office alternative - wps office

Frequently Asked Questions

Why are my PDFs being saved in the Documents folder instead of my chosen location?

If you do not specify a complete directory path (e.g., providing just the filename) in the ExportAsFixedFormat method, Excel will save the PDFs to its default working directory, which is usually the Documents folder. Always concatenate your target folder path with the filename variable.

How can I prevent the PDF from opening automatically after it is saved?

Set the OpenAfterPublish parameter to False in your ExportAsFixedFormat statement. This stops your system's default PDF viewer from launching for every individual scorecard generated.

What happens if cell M5 is empty on one of the worksheets?

If the cell referenced for the filename is empty, the macro will attempt to save the file without a specific name or with just the '.pdf' extension, which may cause a runtime error. It is highly recommended to add an If statement to verify that ws.Range("M5").Value <> "" before executing the export.

Can I export only specific worksheets instead of all of them?

Yes. You can add a conditional check inside the loop. For instance, you can check the sheet's name to ensure only the required scorecard worksheets are processed by wrapping your export code in an IF statement like: If ws.Name Like "Scorecard*" Then.

Excel & Data Problems