VBA & Macro Problems

How to Save an Excel Worksheet as a PDF in a Folder Matched by Cell F1 Using VBA

Anum Mushtaq Oct 22, 2026 868 views

Question details

The user needs a VBA macro to automatically find a specific network folder whose name begins with the text in cell F1 and export the active Excel worksheet into that folder as a PDF.

How to Save an Excel Worksheet as a PDF in a Folder Matched by Cell F1 Using VBA

Product: Microsoft Excel
Device & OS: not provided
Scenario: Automating the export of an Excel worksheet to PDF into dynamically named folders, particularly when folder names include a unique identifier followed by additional descriptive text.
Observed behavior: The macro will search the specified directory for a folder matching the wildcard string derived from cell F1. If found, it exports the active worksheet as a PDF into that specific folder; otherwise, it displays an error.

Before you start

Ensure that the Developer tab is enabled in your Excel ribbon and verify that you have the correct network path where your target folders are located.

Solution 1Recommended

Use a VBA Macro with a Wildcard Directory Search

This solution uses the VBA Dir function combined with a wildcard to locate the exact folder path based on cell F1's value, handling trailing backslashes before exporting the active sheet as a PDF.

This macro is highly effective when your network folder names combine a unique ID (stored in F1) with variable descriptive text. By utilizing a wildcard (*), the code identifies the correct folder even if you do not know the full folder name.

Open the VBA Editor

In your Excel workbook, press the ALT + F11 keys simultaneously on your keyboard to open the Visual Basic for Applications (VBA) editor.

Insert a New Module

Click on 'Insert' in the top menu bar, and then select 'Module'. This will open a blank code window where you can write your macro.

Paste the VBA Code

Copy and paste the following VBA code into the module window: Sub SaveAsPDF() Dim strParent As String, strFolder As String, strFile As String strParent = "\\MyServer\MIS$\Dept-Desktop\Documentation\RFS\Requests\Assessments\" strFolder = Dir(strParent & Range("F1").Value & "*", vbDirectory) If strFolder = "" Then MsgBox "Folder does not exist!", vbExclamation Else If Right(strFolder, 1) <> "\" Then strFolder = strFolder & "\" strFile = "MyInfo for " & Range("F1").Value & ".pdf" ActiveSheet.ExportAsFixedFormat Type:=xlTypePDF, Filename:=strParent & strFolder & strFile End If End Sub Make sure to update the 'strParent' variable to reflect your actual network directory.

Run the Macro

Close the VBA editor and return to your worksheet. Press ALT + F8, select the 'SaveAsPDF' macro, and click 'Run'. The active worksheet will automatically be saved as a PDF in the matching folder.

Use a VBA Macro with a Wildcard Directory Search

Error Handling Included: The macro includes a safety check that will display a warning message box if the target folder does not exist, preventing the script from crashing during the saving process.

Automate with WPS Office

Run VBA Macros and Export to PDF Seamlessly with WPS Office

WPS Spreadsheet fully supports VBA macros, allowing you to easily automate tasks like saving worksheets to dynamically named PDF folders. It offers a familiar interface, high compatibility with Excel macro files (.xlsm), and a built-in PDF converter.

1. Open Your Workbook in WPS Spreadsheet: Download and install WPS Office, then open your macro-enabled Excel workbook (.xlsm).
2. Access the VBA Editor: Navigate to the Developer tab on the top ribbon and click on the 'Visual Basic' button to launch the VBA editor.
3. Insert the Export Macro: Click Insert > Module, paste your PDF export macro script into the code window, and adjust the network path as needed.
4. Execute the Code: Run the macro directly from the interface to automatically generate and save your PDF to the designated folder.

Fully supports VBA macros for complex automation and dynamic PDF exports.High compatibility with Microsoft Excel formats, including .xlsx and .xlsm.Built-in PDF conversion tools to easily generate and format documents.Lightweight, fast-loading, and completely free to use.

microsoft office alternative - wps office

Frequently Asked Questions

Why am I getting a 'Folder does not exist' error when running the macro?

This warning occurs if the network path defined in 'strParent' is incorrect, if you lack network permissions to access the directory, or if no folder within that directory begins with the exact text currently found in cell F1.

Can I save a specific cell range instead of the entire worksheet?

Yes. You can modify the macro by replacing `ActiveSheet.ExportAsFixedFormat` with a specific range reference, such as `Range("A1:D10").ExportAsFixedFormat`. This will export only the selected cells as a PDF.

Does this macro work on macOS versions of Excel?

The provided VBA macro uses Windows-specific directory paths (using backslashes) and the standard Dir function. To run a similar automation on a Mac, you must rewrite the code using Mac-compatible file path syntax (using colons or slashes) and AppleScript integrations.

How do I save the PDF file with a customized name?

In the macro code, locate and modify the 'strFile' variable. For example, change it to `strFile = "Monthly_Report_" & Range("F1").Value & ".pdf"` to customize the output filename according to your reporting requirements.

Excel & Data Problems