BookmarkPrint
Contents
Home > Microsoft Office > Excel > Global Macro - Excel


Global Macro - Excel
How to convert an existing macro into a global macro. If you created a macro in a workbook, and you stored it in that workbook, it will not be available to use anywhere else. Instead of recreating the macro in other workbooks, you can convert it to a global macro that is stored in your personal macro workbook. The workaround involves creating a dummy global macro that will help the old macro become global. This process also works in Excel 2016, 2013, and 2010.

Step 1:You must have already created the macro and saved the workbook that contains it. Open this workbook and keep it open.Step 2:Open a new workbook and click the View tab.

Step 3:
Select the drop down list under Macros and choose Record Macro...

Step 4:Give the macro a name (you may just call it "Dummy"). You do not need to enter a shortcut key. Under Store macro in, choose Personal Macro Workbook from the dropdown list.
Step 5: Click OK.
Store Macro in Personal Macro Workbook

Step 6:
In the active cell, type "This is a dummy," and press the [Enter] key.

Step 7:If you're not already there, click on the View tab. Select the Macros dropdown list and choose Stop Recording.

Step 8:Save the Excel file.

Step 9:In the same file, click the View tab and then click Switch Windows from the Window group. Select the workbook that contains the old macro that you wish to convert.

Step 10:Under the View tab, click the Macros button.
Step 11:
Select the old macro that is not the dummy (it will not contain PERSONAL.XLSB). Click Edit.

Step 12:You can maximize the window if needed. Highlight everything from Sub to End Sub. Right click on the text and choose Copy.

Step 13:Click the Excel icon in the upper left corner to go back to the workbook.

Step 14:Choose the View tab and click on Switch Windows. Select the workbook that contains the dummy macro.

Step 15:Under the View tab, click Unhide from the Window group.

Step 16:Choose PERSONAL.XLSB and click OK.

Step 17:A blank document titled PERSONAL.XLSB will appear. Under the View tab, click the Macros button.

Step 18:Select your dummy macro and click Edit.

Step 19:Highlight everything from Sub to End Sub. Right click on the text and choose Paste. The real macro will replace the dummy macro.

Step 20:Click the Excel icon in the upper left corner to go back to the workbook.

Step 21:Save the PERSONAL.XLSB file
 
Step 22:Whenever you want to apply the old macro in a different workbook, it will now be listed to run.

Related Articles


helpdesk.etown.edu/helpconsole2012/kb/default.aspx?pageid=global_macro