VBA & Macro Problems

How to Use VBA to Name a PDF from an Excel Named Range

Steve K Oct 15, 2026 871 views

Question details

The user needs an Excel VBA macro that prompts for a save location and automatically pre-fills the PDF file name based on a specific named range.

How to Use VBA to Name a PDF from an Excel Named Range

Product: Excel
Device & OS: not provided
Scenario: Printing grouped worksheets as a PDF and asking the user to select a save location via a dialog box.
Observed behavior: The user wants the save dialog to use the 'Review Month' named range value as the default file name instead of a generic or blank name.

Before you start

Verify that your workbook contains the named range 'Review Month' and that its cell value does not contain characters that are illegal in file names, such as slashes or asterisks.

Solution 1Recommended

Use Application.GetSaveAsFilename to Pre-fill the Save Dialog

You can dynamically build the default file name using the Range object and pass it to the GetSaveAsFilename dialog prompt before executing the PDF export.

This solution utilizes Excel's built-in file save dialog. By extracting the value from your named range, you can combine it with the '.pdf' extension to create an automated initial file name without fully bypassing the user's choice of folder location.

Extract the named range value

In your VBA script, reference the named range using Range("Review Month").Value to retrieve the text you want to use for the file name.

Configure the save dialog

Use the Application.GetSaveAsFilename method. Set the FileFilter to 'Adobe PDF File (*.pdf), *.pdf' and set the InitialFileName to your extracted range value concatenated with '.pdf'.

Handle user cancellation

Add a condition to check if the user clicked 'Cancel' in the dialog. Use 'If fname = False Then Exit Sub' to prevent runtime errors when the save action is aborted.

Save or export the file

Once a valid file path is returned in the 'fname' variable, use ActiveSheet.ExportAsFixedFormat Type:=xlTypePDF, Filename:=fname to generate the actual PDF document.

Use Application.GetSaveAsFilename to Pre-fill the Save Dialog

Alternative Save Format: If you intend to save the workbook itself as a macro-enabled Excel file (.xlsm) instead of a PDF, use ThisWorkbook.SaveAs FileName:=fname, FileFormat:=52 as requested in the original snippet context.

Powerful Spreadsheet Automation

Run Macros and Export PDFs with WPS Spreadsheet

WPS Spreadsheet provides comprehensive support for VBA macros, enabling you to automate renaming and exporting documents as PDFs effortlessly.

1. Open your file in WPS Spreadsheet: Launch WPS Office and open the workbook containing your named ranges.
2. Access the VBA Editor: Navigate to the 'Developer' tab on the ribbon and click 'VBA Editor', or simply press Alt + F11 on your keyboard.
3. Insert the macro code: Right-click your workbook project in the editor, select 'Insert', then 'Module', and paste your GetSaveAsFilename script.
4. Run the automated save process: Press F5 or run the macro directly from the interface to bring up the PDF save dialog with your pre-filled name.

Full support for executing VBA macros and scriptsSeamless compatibility with Microsoft Excel formats (.xlsx, .xlsm)Built-in PDF conversion features without requiring extra pluginsLightweight performance and highly familiar user interface

microsoft office alternative - wps office

Frequently Asked Questions

How do I create a named range in Excel for my macro?

Select the cell or range of cells you want to name. Click the Name Box located to the left of the formula bar, type the desired name (e.g., 'Review Month' without quotes), and press Enter.

Why does my VBA macro fail with a runtime error when trying to save?

This commonly happens if the named range cell contains characters that Windows operating systems do not allow in file names, such as <, >, ?, [, ], :, |, or *. Ensure your cell value is clean text before running the macro.

Can I save specific grouped sheets as a PDF instead of just the active sheet?

Yes. Before calling the export function in VBA, use the Sheets(Array("Sheet1", "Sheet2")).Select command to group the required worksheets, then call ActiveSheet.ExportAsFixedFormat to print the selected grouped sheets as a single PDF.

Excel & Data Problems