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.

Tip

When working with macros in Excel 2013, the Developer tab should be active on the Excel Ribbon.

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 1: Display the Macro

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

Image Credit: Ron Price

Step 2: Select the Macro

Image Credit: Ron Price

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.

Tip

You can also press the "ALT" key and the "F11" key together (ALT+F11) to open the VB editor from the anywhere in an Excel workbook.

Step 3: Copy the VBA Code

Image Credit: Ron Price

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.

Step 4: Paste the Code

Image Credit: Ron Price

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 1: Export a Macro

Image Credit: Ron Price

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

Step 2: Save the File

Image Credit: Ron Price

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.

Step 3: Import the BAS File

Image Credit: Ron Price

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 1: Create the Personal.xlsb File

Image Credit: Ron Price

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.

Step 2: Record a Macro

Image Credit: Ron Price

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.

Step 3: Save the Personal.xlsb File

Image Credit: Ron Price

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.

references