Move a macro in Word and Excel
Often you will get a macro delivered to you in a document or worksheet. So you can use these macros every day for your own work you will need to move them into a file that is open every time automatically for you.
For example, I write two macros every month for Techlife magazine in Australia – these are Word, Excel or PowerPoint macros and they are delivered in a Word document, an Excel Worksheet or a PowerPoint file.
Macros you won't move
Macros you can move
Move a Word Macro
To do this, first open the file that contains the macro code, then make sure that the Developer tab is visible.
In Word 2007 to show the Developer tab on the Ribbon, choose the Office Button > Word Options and then from the Popular group, select Show Developer tab in the Ribbon.
In Word 2010 to show the Developer tab on the Ribbon, choose File > Options > Customize Ribbon and in the right hand panel, make sure that the Developer option has a checkmark against it.
Now locate the Developer tab and click the Visual Basic button to open the Visual Basic Editor.
Make sure that the Project Explorer is visible on the left of the screen. If not, choose View > Project Explorer to display it.
Open up the Forms and Modules areas to see what is there.
Typically if the macro includes a Form or dialog then there will be something in the Forms area in the file.
Right click the form in the Forms group and choose Export File. Export this to a folder on your disk that you can find easily later on.
Then go to the Modules area and locate the modules here. This might be called Module1 but it could be called anything. Right click the module and choose Export file and export it to the exact same location as you used to export the form.
Now you have exported the files you need to import them into your Normal file. Click the entry for Normal in the Project Explorer, right click it and choose Import File.
Go ahead and import the files that you just exported. These will appear automatically in the Forms and Modules areas.
You can now close the Word file containing the macro and test the macro on a new Word document.
You can now run the macro at any time from the macros list in any document.
Move an Excel macro
If the macro is one that has a general application you need to copy it to your personal.xls or personal.xlsb file. But to do so you need to have this file – so if you don't, here's how to create one.
Show the Developer Tab
In Excel 2010 to show the Developer tab on the Ribbon, choose File > Options > Customize Ribbon and in the right hand panel, make sure that the Developer option has a checkmark against it.
Create a Personal.xlsb file
Copy the macro code
Go to the Visual Basic Editor by clicking the Visual Basic button in the Developer panel.
Open up the Forms and Modules groups. Most macros have content in the Modules area and some will also have Forms. If a macro includes a form, right click the form name and select Export File and locate a place on your computer to store the file.
Then go ahead and do the same thing to the module by right clicking each module file, choose Export File and save it in the same place as you saved the other file.
Now go to your Personal.xlsb workbook which you will find in the VBA project list – if this isn't visible choose View > Project Explorer.
Right click on the VBA project Personal.xlsb file and choose Import File and go ahead and import the form and module files that you just saved.
You can now close the Excel file containing the macro and test the macro on a new Excel file.
(c) 2017, Helen Bradley, All Rights Reserved.