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

Get FREE Advanced Excel Exercises with Solutions!

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.

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

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.

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

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

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

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.

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.

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.


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

What is ExcelDemy?

ExcelDemy - Learn Excel & Get Excel Solutions Center provides online Excel training , Excel consultancy services , free Excel tutorials, free support , and free Excel Templates for Excel professionals and businesses. Feel free to contact us with your Excel problems.
Rifat Hassan
Rifat Hassan

Rifat Hassan, BSc, Electrical and Electronic Engineering, Bangladesh University of Engineering and Technology, has worked with the ExcelDemy project for almost 2 years. Within these 2 years, he has written over 250 articles. He has also conducted a few Boot Camp sessions on effective coding, especially Visual Basic for Applications (VBA). Currently, he is working as a Software Developer to develop and deploy additional add-ins to enhance the customers with a more sophisticated experience with Microsoft Office Suits,... Read Full Bio

We will be happy to hear your thoughts

Leave a reply

Advanced Excel Exercises with Solutions PDF