Share Macro to all spreadsheets on workstation

I have written a VBA macro in Excel which I need make available to any spreadsheet I open.  how can I do this?  We are using Excel 2007 on XP and the user downloads .xlsx spreadsheets which she will need to run the macro on.
Who is Participating?
byundtConnect With a Mentor Commented:
If you put a macro in your Personal.xlsb macro workbook, then it will be available to any other spreadsheet. The Personal.xlsb workbook is opened automatically whenever Excel launches.

First, check to see if you already have one. To do this:
1.  ALT + F11 to open the VBA Editor
2.  Look in the Project Explorer pane on the left for a VBA Project named Personal.xlsb
3.  If you see it, click the + sign next to Modules under Personal.xlsb
4.  Paste the code in one of those module sheets

If you don't have a Personal.xlsb, it is easy to create one:
1.  Use the Macro Recorder to record a macro
Macro Recorder button2.  In the first step of the wizard, choose to store the macro in "Personal Macro Workbook"
Store macro in Personal macro workbook3.  Stop recording the macro (it doesn't have to do anything). You do this by clicking the same Macro Recorder button as in step 1.
4.  Copy code for any macros you want to be available all the time into a module sheet in the newly created Personal.xlsb workbook
Gregory MillerConnect With a Mentor General ManagerCommented:
Here are some notes on where to find the Personal Macro Workbook since you have an earlier version than the example of 2013 above.
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.