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

Skill Level: Beginner

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

9 comments

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

  • 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

  • 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.

  • 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?

JOIN US & LEARN EXCEL

Learn 10 great Excel techniques that will wow your boss and make your co-workers say, "how did you do that??"
Plus weekly updates to help you learn Excel.

Download the eBook

About Me

Jon Acampora Profile

Hello and welcome! My name is Jon Acampora and I'm here to help you learn Excel.

This blog is updated frequently with Excel and VBA tutorials & tools to help improve your Excel skills and save time with your everyday tasks. Subscribe above to stay updated. More about me...

MVP_Horizontal_BlueOnly