How to Copy Macros in Excel

Macros are one of the game changing features for Excel users who need to perform repetitive tasks on a daily basis. What I personally like most about Macros is that once you record a Macro, it allows you to run it in any workbook and at any time you want. So, why not copy the captured Macros and use them in all other workbooks? Check out these four effective ways to share your macro in Excel.

Method 1: Drag Macro Module

Firstly, the easiest way to copy Macros from one workbook to another is by dragging the Macro Module. This is my go-to method as it reduces the possible errors when copying the code.

Before you begin, open two Excel Workbooks. One with the Macro and the other workbook to copy. Then, make sure you have added the Developer tab and follow the given steps.

1. On the Macro workbook, go to Developer tab and select Visual Basic from the code section.
   
2. On Microsoft Visual Basic for Applications window, enter Ctrl + R to bring up the Project Explorer menu.
3. Now, double-click on the Module to check the Macro code. Once you find the VBA code to copy, drag the Module up or down to another Book. Here, we dragged Module 2 of VBAProject(INDIRECT function.xlsx) to VBAProject(Find Lowest Number.xlsx) workbook.
   
4. Finally, you can run the copied Macro. 

Method 2: Copy the Source Code

Another method to copy Macros is by using the Copy-Paste Command. When you have a lot of Modules in the VBA, you might have trouble dragging it. So, here, we will copy the VBA code and paste them into a new module.

Remember, most users tend to miss codes during the selection. You need to make sure to copy the exact code to avoid errors. Do not skip even the punctuation or quotation marks.

1. Firstly, launch both workbooks. 
2. On your Macro workbook, enter Alt + F11 shortcut keys to bring up the Visual Basic tool. 
3. On VBA window, hover over Project Explorer. If you cannot see it, enter Ctrl + R to display the menu.
4. Click on the Module twice to see the Macro code. Select the Entire code in the Module (From Sub to End Sub).
   
5. Press Ctrl + C to copy the code. 
6. Then, below the new Workbook, right-click on Module and pick Insert > Module.
   
7. Now, enter Ctrl + V to paste the code you just copied into the new Module.
   

Method 3: Use Move or Copy Command

If you wish to copy the VBA code directly to an exact sheet in a workbook, this method is for you. Here, we will use the Sheet’s Move or Copy Command to create a copy of Macro. To do this, again, you’ll need to open two workbooks first. 

1. On your workbook, right-click on Sheet with Macro and pick Move or Copy.
   
2. On Move or Copy Window, expand the drop-down menu for To book and pick a Workbook. 
   
3. Below Before sheet, select a Sheet name. Tick the box for Create a copy.
   
4. Click OK.

Method 4: Export and Import Module File

In the above three methods, we copied the Macros and pasted them into our own different workbooks. But, some of you may be looking for ways to share a Macro with other users. In that case, you can Export the file of the Macro Module. This method is also handy to store the Macros on your PC and import them into your workbook whenever you need in the future.

Export Module File

1. Launch Excel workbook containing Macro.
2. From Developer Tab, click on Visual Basic in the Code group. Or, press Alt + F11 shortcut key to bring up the Microsoft Visual Basic for Applications window.
3. On Project Explorer, right-click on the Module and pick Export File.
   
4. On Export File window, select a location. Enter a Name in the File Name field and hit Save.
   

Import Module File

1. On your Workbook, enter Alt + F11 for the Microsoft Visual Basic window.
2. From the Project Explorer panel, right-click on Module and pick Import File.
   
3. Locate the .bas extension file and click Open.
   
4. On Microsoft VBA window, double-click on the Module to see the copied Macro code.