Macros can only be run if the file they are saved in is open. For example, if you have a macro in file A that you want to use frequently in file B, you’d have to open file A every time you want to run that macro (or save it in the personal workbook). Instead, you can save yourself some time and copy the existing macro into the file where you want to use it.
Copy Macros from Another Workbook
Macros that you create in Excel are stored in modules, and you can then copy those modules to other workbooks.
Open both the file that has the saved macro and the one you wish to save it to.
Click the Developer tab in either file.
Click the Visual Basic button.
In the VBA window, the left pane displays the Project Explorer view. This shows all of the open workbooks and the modules containing macros for each file.
Each time you open Excel and record a macro, or a number of macros, they are stored in a module. If you close out and re-open Excel, any new macros are stored in a separate module.
Locate the module with the macro you want to copy. Double-click the module to open it and verify the macro is correct.
Click the module in the original file and drag it to the file you are copying to.
The module is copied into the file.
Click Save on the toolbar in the VBA window.
Close the VBA program window.
Test a Copied Macro
Just like when creating a macro, it’s important to test a macro you’ve copied to another workbook. If you accidentally copied the wrong one, you’ll want to fix your mistake right away.
Select the sheet where you want to run the macro you’ve just copied over.
Click the Macros button on the Developer tab.
In the Macro dialog box, you’ll want to make sure that This Workbook is selected in the Macros menu. This is the best way to verify the macro was copied directly into the active workbook.
Click the Macros in list arrow.
Select This Workbook.
The macro you copied into the file should appear in the list of macros for this workbook.