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.
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 step-by-step 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: Excel VBA: Close Workbook Without Saving
2. Excel VBA to Save and Close Specific Workbook
For the second method, we will 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.
3. Save and Close Specific Workbook in a 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 save 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: Check If Workbook Is Open and Close It
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 the 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 save 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: How to Close Workbook at Specific Time Using Excel VBA
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 save 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 the Workbook.
Download Practice Workbook
Conclusion
We have shown you 5 quick-and-easy to understand Excel VBA to save and close the Workbook. If you face any problems, feel free to comment below. Thanks for reading, keep excelling!
