How to copy macros from one workbook to another

In Excel, if you have developed a standardized set of macros for performing specific tasks or automating certain processes, copying them to other workbooks ensures consistency across multiple files. This can be particularly useful when working with similar data or when collaborating with others who may benefit from using the same macros.

Imagine you’re working as a sales analyst in a Car Dealership, and you have two separate workbooks that hold sales data. One workbook includes information about sales of regular cars, while the other workbook focuses on sales of Supercars. In the workbook containing data on regular cars, there’s a Visual Basic Applications (VBA) code written to run a macro that will generate a Clustered Bar Graph based on the data. 

Today’s tutorial will guide you on how to effectively transfer this macro from the regular cars’ workbook to the Supercars workbook, enabling you to plot graphs using the macro in the Supercars sales data workbook.

It is a safe and easy method to copy macros from one workbook to another workbook. To successfully copy macros from one workbook to another and ensure their functionality, it is crucial to maintain consistent formatting within the data. This consistency in formatting ensures that the macros can be seamlessly transferred and operate effectively in the new workbook.

Method 1 – By Exporting and Importing Macros

Step 1 – Open the Module

• Open the workbook containing the VBA code to run the macro.
• Navigate to the Developer tab in your application. If you don’t see the Developer tab, you may need to enable it first. To do this, go to the File tab, select Options, choose Customize Ribbon, and check the box for Developer.
• Click on the Visual Basic button in the Developer tab. This will open the Visual Basic Editor window. 
• Alternatively, you can use the shortcut key Alt+F11 to open the Visual Basic Editor directly.
• In the Visual Basic Editor window, you will see the project explorer pane on the left side. It displays a list of all the modules, forms, and other objects in your project.
• Locate the module you want to open in the project explorer pane. Modules are typically listed under the Modules or Code Modules folder.
• Double-click on the module you want to open. 

Step 2 – Export the module

• Select the module by clicking on it in the Project Explorer pane.
• Go to the “File” menu at the top-left corner of the VBA editor window and click on “Export File”.
• Choose the desired location on your computer where you want to save the exported module file.
• Enter a suitable name for the module file, ensuring you use the appropriate file extension, typically “.bas” for modules in VBA.
• Click the “Save” button to export the module to the specified location on your computer.

Step 3 – Open the second workbook

• After exporting the module, close the current book.
• Then, open the other workbook in which we want to import the module to run the macro.

Step 4 – Import the module and run the code

• To open the Visual Basic Editor, navigate to the Developer tab in Microsoft Excel, and click on the “Visual Basic” option. 
• Alternatively, you can use the shortcut key “Alt+F11” to open the Visual Basic Editor window.
• Go to the “File” menu at the top-left corner of the VBA editor window and click on “Import File”.
• Then, select the module that we just exported in the last step and double-click on it to import it.
• Then open the module by double-clicking on it.
• You will see that the code to run the macro has appeared.
• Now your macro will run in this workbook as well.

Method 2 – By copying the VBA code

It is the simplest way to copy the macros from one workbook to the other. To ensure the successful transfer and functionality of macros from one workbook to another, it is essential to maintain consistent formatting within the data. Consistency in formatting guarantees a smooth transition of the macros and enables them to operate effectively in the new workbook.

Step 1 – Open the Module

• Open the workbook containing the VBA code to run the macro.
• Navigate to the Developer tab in your application. If you don’t see the Developer tab, you may need to enable it first. To do this, go to the File tab, select Options, choose Customize Ribbon, and check the box for Developer.
• Click on the Visual Basic button in the Developer tab. This will open the Visual Basic Editor window. 
• Alternatively, you can use the shortcut key Alt+F11 to open the Visual Basic Editor directly.
• In the Visual Basic Editor window, you will see the project explorer pane on the left side. It displays a list of all the modules, forms, and other objects in your project.
• Locate the module you want to open in the project explorer pane. Modules are typically listed under the Modules or Code Modules folder.
• Double-click on the module you want to open. 

Step 2 – Copying the code

• After opening the module, click anywhere inside the code area.
• Then, use the “Ctrl+A” shortcut key to select every line of the code.
• Now, use the “Ctrl+C” shortcut key to copy the code to the clipboard.

Step 3 – Open the second workbook

• After copying the code, close the current book.
• Then, open the other workbook in which we want to paste code in the module to run the macro.

Step 4 – Insert a module

• For adding a module, navigate to the Developer tab.
• After that, click on the first option named Visual Basic.
• You’ll see a new window on your screen and you can also open this window by using the shortcut key (Alt+F11) as well.
• Then, click on the Insert tab in this window and click on the Module option.
• Now, a new module would open.

Step 5 – Paste the code and run it

• Paste the copied code in the module’s code area.
• Close the VBA Editor.
• Now your macro will run in this workbook as well.