How to move a macro in Word and in Excel

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
In some cases, a macro will be designed only to run within the current file and it won't be a macro that you would want to use in all files. Macros which fall into this category include macros that are specific to a particular worksheet which wouldn't make sense used elsewhere.

Macros you can move
On the other hand, many macros are ones you will want to use over and over again with all sorts of files. When this is the case, you need to move the macro into a file that gives you access to that macro at any time in future.

Move a Word Macro
If you have a macro in a Word document then you need to move the macro to your Normal.dotx file.

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.

Now find the file that you just opened which has the macros in 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
Many of my Techlife Excel macros of the month can be used on any worksheet at any time as can any macro in an Excel file.

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
First make sure you have the Developer tab visible on the Ribbon. If not, in Excel 2007 choose the Office Button > Excel Options and then from the Popular group, select Show Developer tab in the Ribbon.

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
Now, to create a personal.xlsb file, from the Developer tab on the ribbon, choose Record Macro and choose to store the macro in your Personal Macro Workbook. The macro doesn’t have to do anything you just have to create it so click in a cell and click Stop Record. Now you have a Personal Macro Workbook and you're ready to go.

Copy the macro code
To copy macros, including my Macro of the Month, into that workbook, open the workbook that contains the macro.

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.