How Do I Import Macros for Excel?

Excel macros are associated only with the workbook in which they are created. However, it isn't necessary to recreate a good macro in another workbook to apply the same actions. Macros can be copied, moved or made available to other workbooks.

Copy a Macro from One Workbook to Another

Perhaps the simplest way to "import" a macro from one workbook into another is using Copy and Paste.

Step

Select the Developer tab and click on the Macros button to display the Macro dialog box.

Click on the Macros button to display the Macro dialog box from which you can select the Macro to copy.
credit: Ron Price
Use the Edit button to display the VB editor and the macro selected.
credit: Ron Price

Step

On the Macro dialog box, select the macro you wish to display in the Visual Basic (VB) editor and click on the Edit button to display the macro's VBA code.

Highlight and copy only the statements, omitting the name of the function and the end line.
credit: Ron Price

Step

In the VB editor, select only the programming code statements of the macro; don't include the macro's title or the last line. Click on Edit and select Copy from the drop-down menu to copy the highlighted code.

Click on the Create button to display the VB editor.
credit: Ron Price

Step

Open the workbook to which the macro is to be added. On the Developer tab, click on the Macros button. Assign a name to the macro and then click on the Create button to display the VB editor. When the VB editor opens, place the cursor on the blank line between the two statements. Click on Edit and choose Paste from the drop-down menu to insert the copied code. Click on File and Save to save the workbook and macro.

Export and Import a Macro

This method creates a VB code file (BAS file) that can be exported so it can be imported into other Excel workbooks.

Step

Open the VB editor and display the macro you wish to export. Click on File and choose Export File.

Choose Export File to save the macro as a .BAS file.
credit: Ron Price
Save the BAS file
credit: Ron Price

Step

On the File Save dialog box, navigate to the folder in which the file is to be saved, name the file, and click Save. The file is now available to be imported into other workbooks.

An imported .BAS file is added as a module to a VB project.
credit: Ron Price

Step

To import a BAS file and add it to a workbook project, select the Developer tab and click on the Visual Basic button to open the VB editor. On the VB editor, click File and then click Import File to display the Import File dialog box. Navigate to the appropriate folder and select the file to be imported. Click Open to import the file.

Creating the Personal Macro Workbook

When Excel starts up, it opens a Personal.xlsb file, if present, as a hidden workbook. This file contains macros available to any Excel workbook running on one computer. Creating a Personal.xlsb file provides a macro library of the macros available on that computer.

Step

To create the Personal.xlsb file, open a New workbook in Excel. On the Developer tab, click on the Record Macro button to display the Record Macro dialog box.

On the Record Macro dialog box, a workbook can be stored as a Personal Macro Workbook
credit: Ron Price
The Stop Button is displayed while a Record Macro function is active.
credit: Ron Price

Step

Assign a name to the macro, then select Personal Macro Workbook on the "Store macro as" option. Click OK to start the recording. Perform the actions to be included in the macro. When the actions are completed, click on Stop Recording on the Developer tab or click the Stop Recording button on the Status Bar in the lower left-hand corner of the workbook.

Save the Personal.xlsb file
credit: Ron Price

Step

After completing the macro, click on the Visual Basic button on the Developer tab to open the VB editor. Select VBAPROJECT (PERSONAL.XLSB) in the Project pane. Click on File and then the Save PERSONAL.XLSB option.