Disclosure: This post may contain affiliate links, meaning when you click the links and make a purchase, we receive a commission.

How to Save a Macro for All Workbooks in Excel (with Easy Steps)

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.


Download Practice Workbook

Download this practice workbook to exercise while you are reading this article.


7 Easy Steps to Save a Macro for All Workbooks in Excel

To save a Macro in all the workbooks is easy. Follow the step-by-step mentioned below for easy learning and smooth understanding.


⧭ Step 1: Opening Developer Tab to Save the Macro

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.

Opening Developer Tab to Save a Macro in Excel for All Workbooks


⧭ Step 2: Start Recording the Macro to Save in All Workbooks

Then Click on the Record Macro button from the code section of the Developer tab.

Start Recording a Macro to Save a Macro in Excel for All Workbooks

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.

Recording Macro to Save a Macro in Excel for All Workbooks


⧭ Step 3: Stop Recording the Macro to Save in All Workbooks

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.


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

Opening the VBA Editor to Save a Macro in Excel for All Workbooks


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


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

Closing the File to Save a Macro in Excel for All Workbooks

So, you are done. You have saved the Macro successfully for all the workbooks on your computer.


⧭ Step 7: Save 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.


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.


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

Rifat Hassan

Rifat Hassan

Hello! Welcome to my profile. Here I will be posting articles related to Microsoft Excel. I am a passionate Electrical Engineer holding a Bachelor’s degree in Electrical and Electronic Engineering from the Bangladesh University of Engineering and Technology. Besides academic studies, I always love to keep pace with the revolution in technology that the world is rushing towards day by day. I am diligent, career-oriented, and ready to cherish knowledge throughout my life.

We will be happy to hear your thoughts

Leave a reply

ExcelDemy
Logo