VBA & Macro Problems

How to Use VBA to Save a Workbook with a Filename from Three Cells

Adam DavisAdam Davis Nov 11, 2026 869 views

Question details

The user wants to create a VBA macro that dynamically generates a filename by combining the text values of three specific cells (A1, B3, and B4) and then saves the workbook into the current directory.

How to Use VBA to Save a Workbook with a Filename from Three Cells
Product
Spreadsheet
Device & OS
not provided
Scenario
Automating the file-saving process so that workbooks are named systematically based on data entered into specific cells, reducing manual data entry.
Observed behavior
The user needs a script to accurately fetch cell values, concatenate them into a proper filename, and execute a save operation to the current directory without causing unintended overwrites or losing the macro code.
Before you start

Before running the macro, verify that the cells you are using for the filename (A1, B3, and B4) do not contain invalid filename characters such as slashes (/), colons (:), or asterisks (*).

Solution 1Recommended

Save the Active Workbook Using Cell Values

Use a VBA macro to combine values from specific cells and save the active workbook in the same folder without overwriting other open files.

This VBA script extracts values from cells A1, B3, and B4 on the first worksheet, concatenates them with underscores, and saves the active workbook in its original directory.

Note that saving with the format `xlOpenXMLWorkbook` will convert the file to a standard `.xlsx` format, which removes the macro. If you want to keep the macro in the saved file, you must change the format to `xlOpenXMLWorkbookMacroEnabled` and the extension to `.xlsm`.

1
Open the VBA Editor

Press Alt + F11 on your keyboard to open the Visual Basic for Applications (VBA) editor.

2
Insert a New Module

Click on 'Insert' in the top menu and select 'Module' to create a blank script window.

3
Enter the VBA Code

Define variables for the folder path and filename. Set the folder path using `ThisWorkbook.Path`. Create the filename by combining `Range("A1").Value`, `Range("B3").Value`, and `Range("B4").Value`. Use `ActiveWorkbook.SaveAs` to execute the save.

4
Run the Macro

Close the VBA editor. In your worksheet, press Alt + F8, select your new save macro, and click 'Run'.

Save the Active Workbook Using Cell Values
Avoid Looping Application.Workbooks: Avoid scripts that loop through `Application.Workbooks` to save every open file with the same concatenated filename. Doing so will cause severe errors and overwrite your other open workbooks.
Efficient Spreadsheet Automation

Automate Your Work with Macros in WPS Spreadsheet

WPS Office provides robust support for VBA macros, allowing you to automate repetitive tasks like saving workbooks based on cell values. It is highly compatible with Microsoft Excel's VBA environment, enabling a smooth transition.

  1. 1. Download WPS Office: Install WPS Office and launch WPS Spreadsheet.
  2. 2. Open Your Workbook: Load the workbook where you want to automate the saving process.
  3. 3. Access Developer Tools: Navigate to the 'Developer' tab and click on 'Visual Basic' or 'Macro' to open the editor.
  4. 4. Run Your Script: Insert your VBA module to automate your dynamic cell-based filenames easily.
Fully compatible with Microsoft Excel macro formats (.xlsm, .xlsb).Built-in VBA editor for writing, editing, and debugging automation scripts.Lightweight software that executes spreadsheet macros quickly and efficiently.Familiar ribbon interface makes finding developer tools effortless.
microsoft office alternative - wps office

Frequently Asked Questions

What characters are invalid when using cell values for filenames?

Windows filenames cannot contain characters like <, >, :, ", /, \, |, ?, or *. If the target cells (A1, B3, B4) contain any of these, the VBA save macro will throw a runtime error.

How do I save the file as a macro-enabled workbook instead of .xlsx?

To keep the macros intact, change the file extension in your VBA code's filename string from ".xlsx" to ".xlsm". Additionally, you must update the SaveAs FileFormat parameter to xlOpenXMLWorkbookMacroEnabled (or the numerical value 52).

Can I save the file to a different folder instead of the current one?

Yes. Instead of using ThisWorkbook.Path, you can explicitly define the target folder path in your VBA code. For example, set your folder path variable to folderPath = "C:\Users\Username\Documents".

Why does my macro remove the VBA project when saving?

By default, if your script uses FileFormat:=xlOpenXMLWorkbook, the application saves it as a standard .xlsx file. The .xlsx format is designed to be macro-free for security reasons, so it strips out all VBA code upon saving.