Rafiul Haq
In this article, we will show you 5 methods of using Excel VBA to save and close Workbook. As time is very valuable, we can save a lot of time by skipping mundane tasks using Excel VBA. Moreover, We have taken a dataset consisting of 3 columns: “Name”, “Born”, and “Latest Work”. This dataset represents 6 people’s birth year and their latest film information.
Download Practice Workbook
5 Examples to Save and Close Workbook Using VBA in Excel
1. Save and Close Active Workbook by Using Excel VBA
For the first method, we will save and close the Active Workbook using VBA Macro. We will bring up the VBA Module window, type our code and then execute the code to achieve our goal. Without further ado, let us jump into the steps by steps procedure.
Before, typing our code we need to bring up the VBA Module. To do that –
- First, from the Developer tab >>> select Visual Basic.
Alternatively, you can press ALT + F11 to do this too. The “Microsoft Visual Basic for Application” will appear after this.
- Secondly, from Insert >>> select Module.
Here, we will type our code.
- Thirdly, type the following code inside the Module.
Sub Save_and_Close_Active_Workbook()
ActiveWorkbook.Close SaveChanges:=True
End Sub
VBA Code Breakdown
- First, we are calling our Sub Procedure Save_and_Close_Active_Workbook.
- Then, we are referring to our current Workbook as ActiveWorkbook.
- After that, using the Close method we are closing our file.
- Finally, we have set SaveChanges to True, which will save our Workbook upon closing.
Now, we will execute our code.
- First, Save this Module.
- Secondly, click inside our code.
- Finally, press the Run button.
If we go to our Excel Application, we will see our Workbook is closed. Thus, we have successfully saved and closed the Workbook using Excel VBA.
Read More: How to Save a Macro for All Workbooks in Excel (with Easy Steps)
2. Excel VBA to Save and Close Specific Workbook
For the second method, we will use save and close a specific Workbook using another VBA code. Here, we have opened two Workbooks and we will save and close the first Workbook from the left side.
Steps:
- First, as shown in method 1, bring up the VBA Module.
- Secondly, type this code inside that Module.
Sub Save_and_Close_Specific_Workbook()
Workbooks("Macro Save and Close.xlsm").Close SaveChanges:=True
End Sub
VBA Code Breakdown
- First, we are calling our Sub Procedure Save_and_Close_Specific_Workbook.
- Then, we are referring to our first Workbook inside the Workbooks object.
- After that, using the Close method we are closing our file.
- Finally, we have set SaveChanges to True, which will save our Workbook upon closing.
- Thirdly, as shown in method 1, execute the code.
After this, we will see that the first Workbook is closed and only the second Workbook is open. Therefore, we have shown you yet another method of saving and closing a Workbook.
Read More: Excel VBA: Save Sheet as New Workbook without Opening
3. Save and Close Specific Workbook in Specific Folder
For the third method, we are going to save and close a specific Workbook in a folder using Excel VBA.
Steps:
- First, as shown in method 1, bring up the VBA Module.
- Secondly, type this code inside that Module.
Sub Save_and_Close_Workbook_in_Specific_Folder()
Workbooks("Macro Save and Close.xlsm").SaveAs _
Filename:="C:\Users\Rafi\OneDrive\Desktop\Exceldemy\Macro Save and Close.xlsm"
Workbooks("Macro Save and Close.xlsm").Close
End Sub
VBA Code Breakdown
- First, we are calling our Sub Procedure Save_and_Close_Workbook_in_Specific_Folder.
- After that, using the SaveAs method we are saving our file in a specific location.
- Then, we keep the filename the same as the original Workbook.
- Finally, we close our Workbook.
- Thirdly, as shown in method 1, execute the code.
After this, it will save our Workbook inside our defined folder location and close it. Thus, we have shown you another method of saving and closing Workbook in a specific folder using VBA.
Read More: Excel VBA Macro to Save PDF in Specific Folder (7 Ideal Examples)
Similar Readings
- Excel VBA: Save Workbook without a Prompt (with Easy Steps)
- [Fixed!] Why Is Excel Not Saving My Formatting? (7 Possible Reasons)
- How to Save Excel as PDF Landscape (With Quick Steps)
- Excel VBA to Save as File Using Path from Cell (With Quick Steps)
- [Fixed!] Excel CSV File Not Saving Changes (6 Possible Solutions)
4. Inserting Button to Save and Close Workbook in Excel
For the fourth method, we are going to create a VBA button to close and save Workbook in Excel.
Steps:
- First, as shown in method 1, bring up the VBA Module.
- Secondly, type this code inside that Module.
Sub Button_Click_Save_and_Close_Workbook()
ThisWorkbook.Save
Application.Quit
End Sub
VBA Code Breakdown
- First, we are calling our Sub Procedure Button_Click_Save_and_Close_Workbook.
- After that, using the Save method we are saving our Workbook
- Finally, we close our Workbook using the Quit method.
Now, we will insert the VBA button here.
- First, from the Developer tab >>> Insert >>> select Button (Form Control).
- Then, the mouse cursor will change and drag a box inside the Workbook.
After that, the Assign Macro dialog box will appear.
- Then, select “Button_Click_Save_and_Close_Workbook”.
- After that, press OK.
Then, we will see Button 1 in the Workbook.
- Finally, click on the button.
This will save and close our Workbook.
Read More: VBA Code for Save Button in Excel (4 Variants)
5. Save and Close All Open Workbooks Applying Excel VBA
In this last method, we’re going to save and close all the opened Workbooks. This time, we have the same two Workbooks as in method 3, however, this time, we’ll save and close both Workbooks. Here, we will use the For Next Loop to go through our Workbooks.
Steps:
- First, as shown in method 1, bring up the VBA Module.
- Secondly, type this code inside that Module.
Sub CloseAndSaveOpenWorkbooks()
Dim z As Workbook
With Application
For Each z In Workbooks
With z
If Not z.ReadOnly Then
.Save
End If
If .Name <> ThisWorkbook.Name Then
.Close
End If
End With
Next z
.Quit
End With
End Sub
VBA Code Breakdown
- First, we are calling our Sub Procedure CloseAndSaveOpenWorkbooks.
- Then, we are using a For Next Loop to cycle through all Workbooks.
- After that, using the Save method we are saving our files.
- Then, we close all the Workbooks except our current Workbook.
- Finally, we have closed the original Workbook using the Quit property.
- Thirdly, as shown in method 1, execute the code.
Therefore, it will save and close the two Workbooks. We can see the date modified is the same for the two Workbooks. In conclusion, we have shown 5 different Excel VBA Macros to save and close Workbook.
Read More: How to Save Macro to Personal Macro Workbook?
Conclusion
We have shown you 5 quick-and-easy to understand Excel VBA to save and close Workbook. If you face any problems, feel free to comment below. Moreover, you can visit our site Exceldemy for more Excel-related articles. Thanks for reading, keep excelling!
Related Articles
- How to save workbooks that contain Macros
- How to Save VBA Code in Excel (3 Suitable Methods)
- Excel VBA to Print As PDF and Save with Automatic File Name
- Fix Excel Not Responding and Save Your Work
- Excel Macro to Save as PDF (5 Suitable Examples)
- How to Save Excel File in XLSX Format (4 Easy Methods)
