VBA & Macro Problems

How to Export Excel Worksheets as PDF or Separate Workbooks Using VBA

Natalie Taylor Oct 8, 2026 869 views

Question details

The user needs a VBA method to export selected Excel worksheets as PDF files using a specific cell's value for the filename, or to export them as standalone Excel workbooks where all formulas are converted to static values.

How to Export Excel Worksheets as PDF or Separate Workbooks Using VBA

Product: Excel
Device & OS: not provided
Scenario: Automating the extraction of specific worksheet data for reporting or distribution without sharing the entire workbook or exposing proprietary formulas.
Observed behavior: The user wants an automated solution where running a macro successfully isolates the worksheet, applies the correct output format (PDF or new workbook), renames it dynamically, and secures the data by removing linked formulas.

Before you start

Ensure your Excel file is saved as a Macro-Enabled Workbook (.xlsm) and that you have enabled the Developer tab in the ribbon to access the VBA editor.

Solution 1Recommended

Export Worksheet as a PDF with a Cell-Based Filename

Use the ExportAsFixedFormat method in VBA to automatically generate and save a PDF file named dynamically after the value inside a specific cell.

This method is highly effective for generating invoices, reports, or automated receipts where the document name changes based on the data within the sheet.

Open the VBA Editor

Press ALT + F11 on your keyboard to launch the Microsoft Visual Basic for Applications editor.

Insert a New Module

In the top menu, click 'Insert' and select 'Module' to create a blank script window.

Input the PDF Export Code

Paste your macro code. Use a structure like: ActiveSheet.ExportAsFixedFormat Type:=xlTypePDF, Filename:="C:\Desktop\Folder\" & Range("M3").Value & ".pdf", IgnorePrintAreas:=False. Make sure the folder path exists on your PC.

Run the Macro

Press F5 or click the 'Run' button (the green triangle) in the toolbar to execute the code and generate the PDF.

Export Worksheet as a PDF with a Cell-Based Filename

Path Verification: Always verify that the target directory exists and that the cell used for the filename (e.g., M3) does not contain invalid filename characters like /, \, *, or ?.

Solution 2

Export Worksheet as a Separate Workbook (Values Only)

Create a new Excel file containing only the active sheet, converting all formulas to plain text and values to prevent linking errors.

Advanced Macro Support

Automate Excel Tasks with WPS Spreadsheets

WPS Spreadsheets provides excellent compatibility with Microsoft Excel VBA macros. You can run, edit, and execute your custom export scripts seamlessly to automate your PDF generation and workbook extraction without any extra hassle.

1. Open Your Workbook: Launch WPS Spreadsheets and open your existing Macro-Enabled Workbook (.xlsm).
2. Access the VBA Editor: Navigate to the 'Tools' tab on the ribbon and click 'Macro' or 'VBA Editor' to view your scripts.
3. Run Your Export Script: Select your PDF or Workbook export macro from the list and click 'Run' to instantly process your worksheets.

Fully compatible with Microsoft Excel Macro-Enabled Workbooks (.xlsm)Built-in PDF exporter that seamlessly integrates with VBA automationLightweight and runs complex macros faster on large datasetsFamiliar user interface requiring zero learning curve

microsoft office alternative - wps office

Frequently Asked Questions

Why do I get a Runtime Error 1004 when exporting to PDF via VBA?

This error typically occurs if the folder path defined in your VBA code does not exist on your computer, or if the cell used for the filename contains unsupported characters (such as colons, slashes, or question marks). Double-check your directory path and ensure the cell value is a clean string.

How can I export multiple selected worksheets at once instead of just the active sheet?

You can modify your VBA script to loop through the selected sheets using a 'For Each' loop. For example, iterate through 'ActiveWindow.SelectedSheets' and apply the ExportAsFixedFormat or Copy methods to each sheet individually within the loop.

Will converting formulas to values alter my original workbook?

No, if you use the 'ActiveSheet.Copy' command first, VBA creates a brand-new workbook. The conversion code (UsedRange.Value = UsedRange.Value) will only apply to this newly copied file, leaving your original source workbook and its formulas completely untouched.

Does exporting a worksheet to a new workbook preserve its formatting?

Yes. When you use the 'ActiveSheet.Copy' method, Excel replicates the exact layout, including column widths, colors, fonts, and borders. Replacing the formulas with values only affects the cell contents, so your visual formatting remains perfectly intact.

Excel & Data Problems