While working with VBA in Excel, sometimes we need to save a Macro for all our workbooks. In this article, I’ll show you how you accomplish this pretty simply and conveniently.
Step 1: Opening Developer Tab in Excel
Open an Excel workbook, and then open the Developer tab. The Developer tab is by default hidden in the Excel toolbar. If you don’t know how to unhide it, follow the steps mentioned here.
Read More: How to Save Macros in Excel Permanently (2 Suitable Ways)
Step 2: Starting Macro Recording
Then Click on the Record Macro button from the code section of the Developer tab.
A dialog box called Record Macro will open. In the Macro name field, put any name according to your wish (It’s set Macro1 by default).
Then from the Store macro in the checkbox, select Personal Macro Workbook. Then click OK.
Read More: Excel VBA to Save File with Variable Name (5 Examples)
Step 3: Stopping Macro Recording
The moment you click OK, it’ll start recording a Macro. Click on the Stop Recording button to stop recording the Macro. The button is in exactly the same position as the Record Macro button.
Similar Readings
- Fix Excel Not Responding and Save Your Work
- How to Undo Changes in Excel after Save and Close (2 Easy Methods)
- [Fix]: Microsoft Excel Cannot Open or Save Any More Documents Because There Is Not Enough Available Memory
- Excel VBA: Create Invoice and Save PDF Format (with Quick Steps)
- Save Excel Files as Text Without Quotes (4 Methods)
Step 4: Opening Visual Basic Editor to Witness the Macro in All Workbooks
Congratulations! You’ve just recorded a Macro as a PERSONAL.xlsb file to use in all the workbooks. To witness it, press ALT + F11 on your keyboard. The Visual Basic window will open.
There in the right pane, you’ll find a Macro called Module1 in the Modules section under VBAProject (PERSONAL.xlsb). This is the Macro that you just recorded.
Read More: Excel VBA: Save Sheet as New Workbook without Opening
Step 5: Inserting the Necessary Code to Save in All Workbooks
Look Carefully. The inside part of the Macro is empty. Insert your VBA code within it (Within the Sub Macro1 and the End Sub section), the code that you want to find available in all workbooks.
Read More: Excel VBA: Save Workbook in Specific Folder (4 Suitable Examples)
Step 6: Closing and Saving the Macro as an Excel Binary Workbook
Finally, close the workbook. A warning box will ask you if you want to save the PERSONAL.xlsb file or not. Click Save.
So, you are done. You have saved the Macro successfully for all the workbooks on your computer.
Read More: Excel VBA: Save and Close Workbook (5 Suitable Examples)
Step 7: Saving the Macro for All Workbooks in Excel
We’ve seen the step-by-step procedure to save a Macro in all the workbooks on our computer. Now it’s time for the output.
Open any Excel file on your computer. It may be an already used old workbook or a fresh new workbook.
Again press ALT + F11 on your keyboard. The Visual Basic window will open.
And you’ll find the Macro available under the VBAProject (PERSONAL.xlsb) section in the right pane.
In fact, it’ll be available in all the workbooks on your computer.
Read More: Excel Macro Enabled Workbook (5 Easy Ways to Open)
Things to Remember
Actually, what you have done is create a new Microsoft Excel Binary File file called PERSONAL.xlsb on your computer. In case you want to remove it from the Visual Basic pane of your computer, just look for the file on your computer and delete it. Most of the time it’s available in the directory C:\Users\ABFA Computers\AppData\Roaming\Microsoft\Excel\XLSTART of the computer.
Download Practice Workbook
Download this practice workbook to exercise while you are reading this article.
Conclusion
Therefore, this is the process to save a Macro in all the Excel workbooks on your computer. Do you have any questions? Feel free to ask us. And don’t forget to visit our site ExcelDemy for more posts and updates.
Related Articles
- [Fixed!] Excel CSV File Not Saving Changes (6 Possible Solutions)
- How to Save Multiple Tabs in Excel as Separate Files (5 Easy Methods)
- Excel VBA Macro to Save PDF in Specific Folder (7 Ideal Examples)
- VBA Code for Save Button in Excel (4 Variants)
- How to Save Multiple Sheets in Excel (6 Ideal Methods)
- Excel VBA to Print As PDF and Save with Automatic File Name
