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.

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

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.

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

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

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

• [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: Save Sheet as New Workbook without Opening