Hello Excellers and welcome back to another #MacroMonday #Excel blog post in my 2020 Excel Macro series. DO you ever have a lot of workbooks open at once?. It takes a long time to close all of those Excel workbooks, doesn’t it?. So, today I will show you how to close all Excel workbooks at once with a simple Excel VBA Macro.
Starting The VBA Macro.
First, you will need to open the Visual Basic Editor. There are two ways to do this.
- Either by hitting ALT +F11 or
- Selecting the Developer Tab | Code Group | Visual Basic. Both methods have the same result.
Before you begin to write any code you need to decide where to store the code. You have a choice.
- To store your code either in your Personal Macro Workbook or
- Store it in your current workbook
What Is The Difference?
So, there is a difference. If you save the code in your Personal Macro workbook it will be available in any Excel workbooks. If you store it in the current workbook then use is restricted to that workbook. In this instance, I may want to reuse the code so I will store it in my Personal Macro Workbook.
When you select this option then Excel creates (if it is not already created) this workbook and saves the macro in that location. By default, this macro workbook is named Personal.xlsb. This is a really useful macro that I want to reuse over and over again. So, I will make sure to save this in my Personal Macro Workbook.
If you want more details on creating and updating your personal macro workbook then I recommend my blog posts below.
Creating and Updating Your Personal Macro Workbook
Why Is My Personal Macro Workbook Not Loading Automatically?
Create A Shortcut To Your Personal Excel Macro Workbook
Write The Code.
So, I have inserted a New Module into my Personal Macro Workbook. Type Sub then the name of the Excel macro. In this example, I have named my Macro CloseAllWorkbooks.
As you type the name of the Macro and hit return, Excel will automatically insert End Sub. Now all that is needed is the rest of the code in between these two lines of code.
[stextbox id=’info’]
Sub CloseAllWorkbooks()
End Sub
[/stextbox]
Declare Variables.
Next, I need to declare any variables I need for the Macro. Declaring a variable or variables simply allocates Excel memory for it. Essentially this is memory container for this value in Excel. I have only one variable to declare in this Macro.
[stextbox id=’info’]
Dim wbs as workbook
[/stextbox]
Looping Through All Workbooks.
For the next bit of code, Excel will loop though all Workbooks, and prompt the user to save any changes before closing them. Looping continues until all workbooks are closed.
[stextbox id=’info’]
For Each wbs In Workbooks
wbs.Close SaveChanges:=True
Next wb
[/stextbox]
Ending The Macro. Closing All Workbooks.
Finally, once all of the non-active worksheets in the workbook have been hidden, the code finally ends. This instruction was already entered into the module for us when started the type the name of the Macro.
[stextbox id=’info’]
End Sub
[/stextbox]
Copy The Full Excel Macro.
Here is the complete Macro code.
[stextbox id=’info’]
Sub CloseAllWorkbooks()
Dim wbs As Workbook
For Each wbs In Workbooks
wbs.Close SaveChanges:=True
Next wb
End Sub
[/stextbox]