Using a Macro in Other Workbooks

It's important to know what goes on behind the scenes when you create a macro. When you create a macro, Excel stores your keystrokes and mouse actions as a set of instruction on a macro sheet. You can tell Excel to store the instructions in one of the following places:

• The active workbook

• A new workbook

• Personal Macro workbook

Excel stores your macro in the active workbook by default. If your macro works only on the current workbook, store the macro in the active workbook. If your macro works in a new workbook, create a new workbook and store your macros there. If your macro works on any workbook in Excel, store that macro in the Personal Macro workbook. The macros in a Personal Macro workbook are available every time you start Excel. You can open the Personal Macro workbook at any time to display the macro sheet.

When you record a macro and choose Tools, Macro, Record New Macro, Excel opens the Record Macro dialog box. This dialog box is where you can specify where you want to store your macros. Click the Store Macro In drop-down arrow to look at the choices, as shown in Figure 20.8.

Figure 20.8. Choices for where to store your macros.

Select an item in the list and continue creating the macro. Excel stores your macros in the place you specify.