To save the workbook that contains your VBA code, use ThisWorkbook.Save; to save the active workbook, use ActiveWorkbook.Save. To save a workbook under a different name or in a different folder, use its SaveAs method and specify a full path and the appropriate file format.
The quickest way to save a workbook
In Excel for Microsoft 365 on Windows, press Alt+F11 to open the Visual Basic Editor, choose Insert > Module, and add a short procedure such as this:
Sub SaveThisWorkbook()
' Save the workbook that contains this code.
ThisWorkbook.Save
End Sub
Run the procedure from the editor by placing the cursor inside it and pressing F5, or return to Excel, press Alt+F8, select SaveThisWorkbook, and choose Run. The macro saves changes to the current file; it does not create a second copy. If the workbook has never been saved, Excel may ask you to choose a name and location.
Before running any macro that saves or changes files, save a separate copy of your workbook. Excel’s Ctrl+Z undo command does not reverse actions performed by VBA, including a save or a Save As operation.
What Save and SaveAs do
Workbook.Save writes the open workbook’s current changes to its existing file. It normally keeps the same name, location, and file type. It is the right method for a routine that simply saves the workbook a user is already working in.
Workbook.SaveAs saves the workbook with a new file name, location, or format. It changes the identity of the open workbook in Excel: after a successful Save As, later saves go to the new file. This matters when a macro is intended to create an output copy but the original workbook should remain the active file. SaveAs is not the same as exporting a separate copy while leaving the original open.
Choose the workbook object deliberately. ThisWorkbook means the workbook containing the running VBA code. ActiveWorkbook means the workbook currently active in the Excel window, which might be a different file. If a macro is stored in Personal.xlsb or an add-in, ThisWorkbook refers to that container, not necessarily the workbook with the data. In that situation, use a clearly selected workbook variable or ActiveWorkbook when that is genuinely the intended target.
Save the current file with a basic macro
This example saves whichever workbook is active when the macro runs. The variable makes the target explicit and avoids repeating the object reference:
Sub SaveActiveWorkbook()
Dim wb As Workbook
' Capture the workbook currently selected in Excel.
Set wb = ActiveWorkbook
If wb Is Nothing Then Exit Sub
' Save changes to that workbook's existing file.
wb.Save
End Sub
Use this approach only when the active workbook is the one you want. A user may click into another open workbook before starting the macro, so a procedure that silently saves ActiveWorkbook can affect an unintended file. For a macro stored in the same workbook it is meant to save, ThisWorkbook.Save is often less ambiguous.
Saving a file that has not yet been named can open Excel’s normal Save dialog. If a macro must save without prompting, give it a valid destination with SaveAs first, or check whether the workbook already has a path and handle the first-save case separately.
Save under a specified name and location
Use SaveAs when the destination is known in advance. The folder must already exist, the user or Excel must have permission to write there, and the extension should match the chosen format. This example saves a macro-enabled workbook to a Windows folder:
Sub SaveMacroEnabledCopy()
Dim wb As Workbook
Dim targetPath As String
Set wb = ThisWorkbook
targetPath = "C:\Reports\MonthlyReport.xlsm"
' Save in the Excel macro-enabled workbook format.
wb.SaveAs Filename:=targetPath, _
FileFormat:=xlOpenXMLWorkbookMacroEnabled
End Sub
Replace the example folder and file name with the real destination. If that file already exists, Excel may display an overwrite confirmation. Do not suppress that prompt unless the macro has a deliberate, safe overwrite policy; otherwise, a run can replace an important file unexpectedly. SaveAs also changes the open workbook’s path and name, so subsequent calls to wb.Save save to the new destination.
For a normal macro-free workbook, use an .xlsx name and xlOpenXMLWorkbook. If the source contains VBA, saving it as .xlsx removes the VBA project from the saved workbook. Keep the .xlsm extension and use xlOpenXMLWorkbookMacroEnabled when the macros need to remain available.
Let the user choose the destination
A Save As dialog is useful when the folder or output name changes from run to run. GetSaveAsFilename displays a file picker and returns the chosen name; it does not itself save the workbook. The macro must check for cancellation and then call SaveAs:
Sub SaveAsChosenXlsm()
Dim wb As Workbook
Dim fileName As Variant
Set wb = ThisWorkbook
' Ask for a destination; the user can cancel the dialog.
fileName = Application.GetSaveAsFilename( _
InitialFileName:=wb.Name, _
FileFilter:="Excel Macro-Enabled Workbook (*.xlsm), *.xlsm")
If VarType(fileName) = vbBoolean Then Exit Sub
' Save the selected path while retaining the VBA project.
wb.SaveAs Filename:=CStr(fileName), _
FileFormat:=xlOpenXMLWorkbookMacroEnabled
End Sub
When the dialog is canceled, the returned value is Boolean False, so the procedure exits without saving. The file filter guides the user toward an .xlsm name, while the explicit FileFormat argument tells Excel which workbook format to write. A filter or extension alone should not be treated as a substitute for choosing the correct format.
Choose a matching Excel file format
The filename extension and the FileFormat value should agree. Common choices include:
.xlsxwithxlOpenXMLWorkbookfor a standard workbook without VBA macros..xlsmwithxlOpenXMLWorkbookMacroEnabledwhen the workbook needs to retain VBA code..xlsbwithxlExcel12for an Excel binary workbook..xlswithxlExcel8for the older Excel 97–2003 workbook format, when compatibility requires it.
These are Excel VBA format constants, not text descriptions to type in place of the constants. Older formats can have feature or size limitations compared with modern workbook formats. If compatibility with another application or older Excel installation is important, confirm that the destination format supports the workbook’s features before converting it.
Changing a workbook from .xlsm to .xlsx is a format conversion, not simply a different label. VBA code cannot be preserved in an .xlsx workbook. A warning from Excel when changing formats is meaningful: cancel if retaining the macros is required, or use .xlsm instead.
Common save errors and safer handling
A save macro can fail for reasons unrelated to the VBA statement itself. Check the destination and workbook state when Excel reports an error. Frequent causes include:
- Folder not found: the directory in the path does not exist. VBA SaveAs does not create missing folders automatically.
- Permission or access issue: the file is read-only, the folder is protected, or a network location is unavailable.
- Invalid or mismatched name: the filename contains characters Windows does not allow, or the extension does not match the FileFormat setting.
- File already exists: Excel may ask whether to replace it. Confirming overwrites the existing destination file.
- Wrong workbook object: ActiveWorkbook may point to another open file, while ThisWorkbook may point to an add-in or Personal.xlsb.
- Format conversion warning: the selected format may not support features such as VBA macros.
For unattended automation, decide what should happen if the destination already exists, a folder is missing, or saving fails. One option is to check that the folder exists and create a unique output name rather than replacing a prior file. Another is to report a clear error and stop. Avoid turning off Application.DisplayAlerts broadly: Excel’s alerts protect users from actions such as overwriting files or losing unsupported features, and disabling them can make a macro destructive if not carefully restored and controlled.
When adding error handling, report the problem rather than silently continuing as if the save succeeded. A procedure that catches an error should still make clear to the user which destination failed and should restore any application settings it changed. A successful VBA call is also not proof that a network backup or cloud sync has finished; Excel saving and external synchronization are separate processes.
Where to put and run the VBA code
For a macro that belongs to a particular workbook, save that workbook as an Excel Macro-Enabled Workbook (.xlsm), open the Visual Basic Editor, and place a regular procedure in a standard module using Insert > Module. Save the .xlsm file after adding the code. Excel’s macro security settings may require you to enable macros for a trusted file; do not enable code from an unknown source merely to run a save macro.
On Windows, Alt+F11 opens the Visual Basic Editor and Alt+F8 opens the Macro dialog. On Mac, the Visual Basic Editor is available through Tools > Macro > Visual Basic Editor; the keyboard shortcut can depend on the keyboard’s function-key settings. Excel for the web does not run VBA macros. Use the desktop Excel app to edit and execute VBA, and note that Excel for the web’s AutoSave behavior is not a replacement for running a VBA procedure.
Frequently Asked Questions
What is the VBA code to save the current workbook?
Use ThisWorkbook.Save when the workbook containing the code is the intended file. Use ActiveWorkbook.Save when you specifically intend to save the workbook currently active in Excel.
How do I save a workbook with a new name using VBA?
Call the workbook’s SaveAs method with a full destination path and a matching FileFormat value. For a macro-enabled file, use an .xlsm extension and xlOpenXMLWorkbookMacroEnabled.
Does ThisWorkbook.Save save the workbook as a new copy?
No. It saves changes to the workbook’s existing file. Use SaveAs to give the open workbook a new name or location; after SaveAs, the open workbook is associated with that destination.
Can VBA save an .xlsm workbook as .xlsx?
Yes, but .xlsx does not store VBA macros. Saving in that format removes the VBA project from the resulting workbook, so use .xlsm if you need the code to remain in the saved file.
Why does my VBA save macro ask me to overwrite a file?
The destination file already exists, and Excel is asking whether to replace it. Choose a different name, confirm replacement only when intended, or add explicit logic to avoid overwriting an existing file.
Can I run a VBA save macro in Excel for the web?
No. Excel for the web does not execute VBA. Open the workbook in the desktop Excel application to run the macro; cloud AutoSave and a VBA Save command are separate mechanisms.
Related
- Protect Sheet in Excel: Allow Editing in Certain Cells
- Jira Merge Table Cells: Steps and Limitations
- Conditional Formatting: Highlight Duplicates in Excel
Try it: the Column Letter to Number: aA is 27, XFD is 16,384. Converts both ways.
More in VBA & macros
- VBA Dictionary Case Insensitive? How CompareMode WorksYes. A VBA Scripting.Dictionary can treat keys as case-insensitive: set its CompareMode property to vbTextCompare before adding any…
- Excel VBA TextBox for Data Entry: Setup and CodeYes. In Excel for Microsoft 365 on Windows, a VBA TextBox can collect information through a UserForm or…
- VBA Convert String to Uppercase with UCase ExamplesUse VBA’s UCase function to convert a string to uppercase; for a result explicitly typed as a String,…
- VBA Create New Workbook: Code, Options, and Tips in ExcelTo create a new Excel workbook with VBA, use Workbooks.Add. For example, Set newWb = Workbooks.Add creates a…
- VBA Error Handler: How On Error Works and When to Use ItIn VBA, an error handler is code that responds when a statement fails while a procedure is running.…
- VBA Scripting.Dictionary: How It Works and When to Use ItA VBA Scripting.Dictionary is an object that stores pairs of unique keys and associated values, letting a macro…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.