When you create a macro or a user defined function in a workbook, by default that macro will only be visible for that specific workbook.
If you have a code that you use repeatedly and you want it to be available for all workbooks then you have to create a global workbook called Personal.xls and save it in Excel startup folder.
How to create the file:
1. Open a new workbook.
2. Save As Personal.xls in folder:
C:\Program Files\Microsoft Office\OFFICE11\XLSTART
3. Copy your macros from their original workbooks to personal.xls.
4. Click Window –> Hide to hide the file.
The path may differ slightly depending on which version of office you are using.
How to use the file:
1. Whenever you start Excel personal.xls will be opened automatically, and all your macros will be available for use in any workbook.
2. Keep personal.xls hidden unless you want to add macros to it.
3. When you want to add new macros Click Window –> Unhide and select “personal.xls”.
4. To add a new macro click Tools –> Macro –> Record new macro. You will get the following dialogue box.
5. Under “Store macro in:” select “Personla.xls”.
6. When you are done hide the file again.
If you are interested in more details please see the following Microsoft office link:
Applies to: Excel 2003