How to Save an Excel Workbook Copy Using VBA While Keeping the Original Open
Question details
The user wants to save a copy of an active Excel workbook to a specific directory using VBA while ensuring the original workbook remains open and active for continued editing.
- Product
- Excel
- Device & OS
- not provided
- Scenario
- Using a VBA macro to automatically back up or duplicate a workbook without interrupting the current workflow.
- Observed behavior
- Using the SaveCopyAs method sometimes yields unexpected results or corrupt files if an incorrect or unsuitable file extension is specified in the VBA code.
Ensure you have the Developer tab enabled in your spreadsheet program and that your current workbook is saved in a macro-enabled format (.xlsm) before writing and executing VBA code.
Use the SaveCopyAs Method with a Valid File Extension
The easiest and most reliable way to create a copy without affecting your active session is using the built-in SaveCopyAs VBA method.
The ActiveWorkbook.SaveCopyAs method saves a copy of the workbook to a specified file but does not modify the open workbook in memory. The key to avoiding corruption is ensuring the file extension matches the workbook's format (e.g., using .xlsm for a macro-enabled file).
Press ALT + F11 on your keyboard to launch the Visual Basic for Applications editor in your spreadsheet program.
Right-click on your workbook name in the Project Explorer window on the left, select Insert, and then click Module.
Create a new macro by typing `Sub SaveCopy()` and pressing Enter. Inside the sub, add your code: `ActiveWorkbook.SaveCopyAs Filename:="C:\YourDirectory\YourFilename.xlsm"`. Be absolutely sure to use the .xlsm extension if your original file contains macros.
Press F5 or click the green Run button at the top to execute the code. A duplicate file will be generated in your target folder, while your original file stays completely open and active.
Use Save As, Close, and Reopen via a Hidden Macro
An alternative approach is to save the file under a new name, close it, and dynamically reopen the original file using a separate hidden macro workbook.
Run VBA Macros Seamlessly in WPS Spreadsheet
WPS Office provides robust native support for VBA macros in its advanced editions, allowing you to automate tasks like saving workbook copies without interrupting your workflow.
- 1. Open your macro-enabled workbook: Launch WPS Spreadsheet and open your .xlsm file.
- 2. Access the Developer tab: Navigate to the 'Tools' tab on the ribbon and click 'Developer' to reveal the macro toolset.
- 3. Launch the VBA Editor: Click 'VBA Editor' to open the environment, paste your SaveCopyAs code, and run it just like you would in Excel.
Frequently Asked Questions
Why does SaveCopyAs corrupt my Excel file or make it unreadable?
This usually happens if you specify an incorrect file extension in your VBA code. For example, forcing a .xlsx extension on a workbook that contains macros will result in a corrupted copy. Always ensure the extension string in your code matches the actual file type.
Does the SaveCopyAs method change my active workbook?
No, SaveCopyAs simply exports a copy to the specified destination in the background. Your original workbook remains open, active, and untouched on your screen.
How do I add a timestamp to the copied filename in VBA?
You can dynamically concatenate the current date and time using the Format function in VBA. For example: `ActiveWorkbook.SaveCopyAs "C:\Backups\MyFile_" & Format(Now, "yyyymmdd_hhmmss") & ".xlsm"`.
