When you create a macro in Excel, by default, the macro works only in the workbook that contains it. But if you need to use a macro in multiple workbooks what do you do?
You can use your own personal macro workbook, called PERSONAL.XLSB. This is a hidden workbook into which you can store macros for use in any open workbook on your computer.
Sounds great, but Microsoft make even this simple thing complicated. Who knows why but they do. By default the PERSONAL.XLSB doesn't exist. This isn't an issue if you are using the macro recorder to create macros, as invoking the recorder allows you to create PERSONAL.XLSB. However if you want to hand code some VBA you first have to jump through some (simple) hoops to create your very own personal macro workbook.
Here's How to Create a Personal macro Workbook
Make sure Excel is open (really! 🙂 ), you don't have to have any workbooks open, just Excel.
If you don't have it already, then follow these instructions to enable the Developer tab.
- In the Developer tab click on Record macro
- Change the Store macro in drop down to Personal Macro Workbook
- Click on Stop Recording in the Developer tab
You now have a new PERSONAL.XLSB. Sorry, the capitals are necessary, that's how Microsoft have named the file.
To make sure, press ALT+F11 in Excel which opens the VB Editor. In the Project Explorer on the left hand side of the screen, you'll see the new PERSONAL.XLSB, with the code for the macro you recorded.
If your Project Explorer isn't visible, press CTRL+R to open it.
In my case I recorded Macro1 but of course there is no actual code as I didn't do anything. I just told Excel to start recording, then stopped recording. This is enough though to achieve what we want. You can delete the macro in Module1 as we don't want it.
Press CTRL+S to save the file. Now when you close Excel and open it again, you have your own PERSONAL.XLSB file to store your most useful and often used macros.
Running The Macros
You have a few options here. You can run the macro from within the VBA editor, but more likely you will want to run the macro from within a workbook. You can press ALT+F8 to bring up your list of macros and choose the one to run.
But you can also create a shape or shortcut to run it.