Excel VBA: Save and Close Workbook (5 Suitable Examples)

Get FREE Advanced Excel Exercises with Solutions!

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.

Dataset: excel vba save and close 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 step-by-step procedure.

Steps:

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.

VBA Module: Save and Close Active Workbook by Using Excel VBA

  • 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.

Code Run: Save and Close Active Workbook by Using Excel VBA

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.

Excel VBA to Save and Close Specific Workbook

Steps:

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.

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:

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

Save and Close Specific Workbook in Specific Folder

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.

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:

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).

Inserting Button to Save and Close Workbook in Excel

  • 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.

Final Output of Inserting Button to Save and Close Workbook in Excel

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:

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

excel vba save and close workbook

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.

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.

Last Step of Save and Close All Open Workbook Applying Excel VBA


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!


Related Articles

What is ExcelDemy?

ExcelDemy Learn Excel & Excel Solutions Center provides free Excel tutorials, free support , online Excel training and Excel consultancy services for Excel professionals and businesses. Feel free to contact us with your Excel problems.
Rafiul Haq
Rafiul Haq

Greetings! I am Rafiul, an experienced professional with an engineering degree and an MBA in finance. Data analysis is my passion, and I find Excel to be an invaluable tool in this domain. My goal is to simplify complex concepts by providing comprehensive and user-friendly guides on Excel and finance topics, aiming to enhance people's productivity and understanding at ExcelDemy.

We will be happy to hear your thoughts

Leave a reply

Advanced Excel Exercises with Solutions PDF

 

 

ExcelDemy
Logo