4

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 and give it a thumbs up.YouTube Thumbs Up LikeYouTube Subscribe Logo Excel Campus

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:

M_TOC.bas (2.3 KB)

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

Please share
Jon Acampora
 

Welcome to Excel Campus! I am excited you are here. My name is Jon and my goal is to help you learn Excel to save time with your job and advance in your career. I've been an avid Excel user and VBA developer for 10+ years. I am also a Microsoft MVP. I try to learn something new everyday, and want to share this knowledge with you to help you improve your skills. When I'm not looking at spreadsheets, I get outdoors and surf. :) more about me

Click Here to Leave a Comment Below 4 comments
Edil - October 12, 2018

Thank again Jon.
I always enjoy your tips, and I always learn something too.

Reply
Mickael - October 12, 2018

Thank you Jon!
I learn so much from you.

Reply
    Jon Acampora - October 12, 2018

    Thank you Mickael! I really appreciate your support. 🙂

    Reply

Leave a Reply: