Create Your Personal Macro Workbook
When you create a macro in Excel it is usually designed to work in just one particular workbook, but there may be some instances where you want to use some Excel macros which are much more general, that you can use in all or at least make them available to use all of your workbooks.
There is place in Excel where you can store these macros and call on them at any time, this is the Personal Macro Workbook. This special workbook is loaded up whenever you start Excel, but it does not exist in until you actually record a macro in Excel and save it in this location. That’s really easy to do with a few steps you can follow below.
- Ensure the Developer Tab is enabled in Excel. If you cannot see it then you can easily enable it. If you need instructions on how to do this then you can read my blog post here.
- Now you can record a Macro to save in the Personal Macro Workbook. (Let’s just make some text bold in our workbook).
An Example Macro
- In cell B2 type some text – in this example, I have typed Hello World.
- Select the Developer Tab – Code Group – Record Macro.
- In the Macro Dialog Box, give the Macro a name- in this example, I have used mytestmacro.
- In the Store Macro in: select Personal Macro Workbook.
- Hit Ok.
- Hit CTRL+B to make your text bold (this is all we want to do for this small macro).
- On the Developer Tab – Code Grouping – Stop Recording.
- Close any workbooks and exit Excel.
- When prompted, save the changes to your Personal Macro Workbook.
- Select to save the workbook.
The next time you start Excel, your Personal Macro Workbook will load, but it is hidden by default, but you can easily view it –
- View Tab
- Window Group
- Unhide
- You should now see PERSONAL.xlsb
Any macros you save to your Personal Macro Workbook will be editable but only when you chose to unhide it. It is also easy to hide the Personal Macro workbook as well.
- View Tab
- Window Group
- Hide
If you create any new macros and save them in your Personal Macro Workbook, or edit any macros that it contains you will again be prompted to save the workbook, just as you did the first time it was saved.
What Next? Want More Tips?
So, if you want more tips then sign up for my Monthly Newsletter where I share 3 Tips on the first Wednesday of the month and receive my free Ebook, 30 Excel Tips.
If you want to see all of the blog posts in the Macro Monday series. Click on the link below
Macro Mondays Blog Posts.
Do You Need Help With An Excel Problem?.
Finally, I am pleased to announce I have teamed up with Excel Rescue, where you can get help FAST. All you need to do is choose the Excel task that most closely describes what you need to be done. Above all, there is a money back guarantee and similarly Security and Non-Disclosure Agreements. Try It!. Need Help With An Excel VBA Macro?. Of course, you don’t need to ask how to list all files in a directory as it is right here for free.
