How to Use VBA to Save a Workbook with a Filename from Three Cells
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.

- 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 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 (*).
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`.
Press Alt + F11 on your keyboard to open the Visual Basic for Applications (VBA) editor.
Click on 'Insert' in the top menu and select 'Module' to create a blank script window.
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.
Close the VBA editor. In your worksheet, press Alt + F8, select your new save macro, and click 'Run'.

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. Download WPS Office: Install WPS Office and launch WPS Spreadsheet.
- 2. Open Your Workbook: Load the workbook where you want to automate the saving process.
- 3. Access Developer Tools: Navigate to the 'Developer' tab and click on 'Visual Basic' or 'Macro' to open the editor.
- 4. Run Your Script: Insert your VBA module to automate your dynamic cell-based filenames easily.

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.




