How to Copy or Import VBA Code to Another Workbook

Bottom Line: Learn how to insert VBA code into your worksheets using four different techniques.

Skill Level: Beginner

Watch on YouTube & Subscribe to our Channel

Download the Basic File

In Option 4 below, I'll show you how to import a basic file (.bas) into your workbook.  The file I use in the example can be found here:

Copying or Importing VBA Code

If you're relatively new to Macros and VBA, then you might want to use code from other sources. This could be from previous projects you created, code you found on the web, or a macro from your own personal macro workbook.

Getting VBA code into your workbooks can be done in four different ways. All of these are are quick and easy to implement.

Option 1 – Copy and Paste VBA Code

The first option is simply to copy the code from whatever source you are taking it from and then paste it into the VB Editor.

  1. Copy the source code using the Ctrl+C keyboard shortcut (or the right-click menu).  In the example below, I've copied some code from this Excel Campus post.Copy VBA Code from source
  2. In Excel, access the VB editor by clicking on the Visual Basic button on the Developer tab.  Or use the keyboard shortcut Alt+F11.
  3. In the Project Pane of the VB Editor, select the workbook you want the code to be in.  Then, if there is not already a code module listed under that sheet, you can insert one by choosing the Insert option, and then Module.  (The keyboard shortcut to do this is ALT+I+M.)
    Insert a Module in the VB Editor
  4. Once the module is added, you can double-click on that new Module1 project to open the code pane if it isn't already open.  That's where you are going to paste the code that you copied in Step 1.
    Paste the code into the VB Editor

Saving Your Workbook as a Macro-Enabled File

Any time you are placing a macro into an Excel file that doesn't already have them, it's important that you save the file as a macro-enabled workbook (.xlsm).  When you go to save your workbook, you may receive this prompt, asking you if you want to ignore the code and save it as a macro-free workbook.  You want to select No.

Information box to save as a macro-free workbook

Then you want to change the file type to be an Excel Macro-Enabled Workbook (.xlsm).  Once you have selected that option from the drop-down menu for the file type, just click Save.

Save file as a macro-enabled workbook

If you do not save your file as a macro-enabled workbook, then the VB code you copied will not be able to run.

Option 2 – Drag and Drop a Module

Another way to insert code into a workbook is to drag and drop a code module from one book to another.

  1. Open both your source workbook and your destination workbook.
  2. From any Excel file, access the VB editor (using the Visual Basic button on the Developer tab, or Alt+F11).
  3. Left-click on the module you want to copy, but don't release your hold until you've dragged the module to the destination file.                                                      Drag and Drop Code Module to another Workbook
  4. The file will not be moved, but copied to the new folder.  You'll be able to see it listed in both workbooks.
    Module duplicated in two workbooks

This drag and drop method can be used for Userform and Class modules as well.

Removing a Code Module

Perhaps you dropped your file prematurely while you were dragging it and the module ended up in the wrong workbook.  If you want to remove a module, the process is simple.  Just right-click on the module you want to remove and choose the option for removing it.

Remove a module

Option 3 – Copy Sheet Modules That Contain Code

Unfortunately, the drag and drop process above does not work for Sheet modules.  However, the process to move or copy sheets (and the code that comes with them) is also very simple.

  1. Open both the source workbook and destination workbook.
  2. Starting on the sheet that you want to make a copy of, right-click on the name tab at the bottom.  Then select “Move or Copy…”.
    Move or Copy a Worksheet
  3. In the Move or Copy Window you want to (1) select the checkbox to create a copy, (2) select your destination file, (3) indicate where you want the sheet to go in the new workbook, and (4) click OK.
    Move or Copy Window

The sheet (and the code that comes with it) is now copied to the destination workbook.

In the example above I'm copying a sheet that has code to make it automatically create or update a table of contents whenever changes are made to the file.  I explain more about this technique in my video on how to create a table of contents that automatically updates.

Option 4 – Export & Import Modules

Finally, you can export and import modules instead of just copying them.  It's a good option for sharing modules with other people.  It allows you to save them to a folder on your computer for future use as well.

  1. In the VB Editor, right-click on the module you want to export.  Then choose Export File….
    Export a Module..
  2. You can save it to any location you choose.  The module will save as a basic file (.bas extension).
  3. When you are ready to import it to another workbook, just right-click on the workbook you want to place it in and choose Import File….
    Import a Module

Conclusion

Importing code modules or macros is as easy as that!

You can use any of these options to copy code to your personal macro workbook as well.  If you haven't yet set up a personal macro workbook, I explain how you can do it and why it's a good idea in this post & video series: How to Create a Personal Macro Workbook.

Checkout this post on VBA Code Modules & How to Run Macros Based on User Events to learn more about the different types of code modules.

I hope these four options help you to be able to better integrate macros and modules into your Excel files.  If you have any questions about them, please let me know by leaving a comment below.

Free Webinar on Macros & VBA

If you are interested in learning more about macros, I'm currently running my free webinar called “The 7 Steps to Getting Started with Macros & VBA”.  It's running all this week, and it's absolutely free to register.

During the webinar I explain why you might want to learn VBA, and a lot of the basic coding concepts that will help you get started.  I jump into Excel and the VB Editor and walk through how to write and run our first macro.  Even if you have been using VBA for awhile, I'm sure you will learn some new tips.

Macros and VBA Webinar Banner - Join Me - 550x200

Click here to learn more and register for the webinar

17 comments

Your email address will not be published. Required fields are marked *

      • from VBE, add the reference to “Microsoft Visual Basic for Applications Extensibility 5.3 library” to the project. then in the declarations section enter:
        Dim FNametxt As String
        Dim VBProj As VBIDE.VBProject
        Dim VBComp As VBIDE.VBComponent
        Dim response As String

        ‘check and see if directory ‘Module_Code’ exists, if not create it.
        Call Is_MC_created
        ‘get the module procedure from the source workbook
        response = InputBox(“What is the name of the module you want to export to the target workbook”, “Name”)
        If ModExists(response) Then
        With ThisWorkbook
        Let FNametxt = .Path & “\Module_Code\module.txt”
        .VBProject.VBComponents(“response”).Export FNametxt
        Else
        MsgBox (“Module ” & response & ” does not exist, exiting procedure”)
        End With
        End If
        ‘set the project to the target workbook
        Set VBProj = ActiveWorkbook.VBProject
        ‘tranfer the module to the target workbook
        VBProj.VBComponents.Import FNametxt

        this will copy the named module to the activeworkbook.

  • Hi John

    Thanks for the video…

    I am struggling to be able to copy and past my VBA code by copying the sheet to an external workbook without the original workbook opening when I apply the Macro.

    Any thoughts?

  • Hello Jon,

    I am new to Excel VBA Macros and would like to know how to Create new sheets based on a date list on another sheet within the same workbook that will be populated daily. I want the sheets to be named after the dates. Kindly assist with the code, like a format.

    The date list is found in two worksheets named “PURCHASES” and “SALES”. I want the newly generated sheets to be named sequentially and if any date is entered in the cells, they should be updated automatically on the worksheet names.

    Also, I want to copy the template on a particular worksheet to the newly generated worksheets and also do some computations.

    Would you mind if I send the excel document?

    I need the code guide please.

    Thank you very much.

    Kind regards,

    Williams S.

  • Was wondering if anyone can help. working in mac osx excel2011. We have excel work book and file tmp with macros. they import cvs file from folder x place into a formatted spreadsheet then further they get sent to printing They work flawlessly . my computer is dying and need to transfer copy etc the workbppk and macros all in place want to save and transfer the workbook and file exactly as is.
    Problem is changing path to files. different computer user name etc

  • You left out one more way of importing and exporting modules and userforms. Simpy automate the VBE and use code to import and export the modules/userforms. it require trusting access to the vba project object model but if you are doing macros all day, this is a minor consideration. i have developed code and userforms to automate this process. let me know if you would like a copy of the code.

    • I would like to have that code – trying to automate copying two modules to over 80 different workbooks. Anything you would be willing to share would be great. You can email me at elliscj at bluediamonde.com – Thanks.

    • I have a sales report with 3 buttons. I want to send this report every month to the sales team, then they can access 3 different kinds of data using these 3 buttons. My problem is macro codes are on my computer, so they cannot access to the macro codes. How can I send these macro codes to them along with the report? Since I have 25 reports to send, copy and paste in manually is not possible. I am pretty new to macro. I appreciate if you can help on this. Thank you!

  • Hi
    I found this very useful and have followed it to the letter however the workbook i have copied to does not act in the same way as the original. Everything seems the same but although part of the macro works, one part doesn’t.
    Basically I have copied a stop watch with start, stop and reset to another workbook. On reset the time for each person is generated in a table. After copying the stop watch works but the transfer of person and time to the table doesn’t.
    Even with both workbooks open, the stop watch operated in the copy starts both stop watches but the person number and time is not transferred to the table in the copy although it is in the original.
    I wondered if you have any solution – I am new to this and don’t know lots about VB and wanted a stop watch so pupils can be timed completing a worksheet and can also see any improvement in times each time they complete it.

    Regards

    Janice

Generic filters
Exact matches only

Excel Shortcuts List

keyboard shortcuts list banner

Learn over 270 Excel keyboard & mouse shortcuts for Windows & Mac.

Excel Shortcuts List

Join Our Weekly Newsletter

The Excel Pro Tips Newsletter is packed with tips & techniques to help you master Excel.

Join Our Free Newsletter