Home > Macros > Basics > Copy Code How t0 Add Code to Excel Workbook How to copy Excel macro VBA code to your workbook, from website or sample file, and where to paste the code. See how to make simple changes to the macro code, if needed. Step-by-step videos, written steps. NOTE: If you want to record your own Excel macro, see the absolute beginner steps to record and test a macro in Excel. Or, see the easy steps to show a message in Excel, using a macro. Author: Debra Dalgleish

Copy Excel VBA Code to a Regular Module

The programming language in Excel, and other Microsoft Office programs, is Visual Basic for Applications (VBA). This page shows how to copy VBA code into your Excel files, and store it in the correct location.

To see the steps for pasting a macro into a workbook, and running the macro, please watch this short video tutorial. The written instructions are below the video.

Copy Excel VBA Code to a Regular Module

Instead of starting from scratch, if you need an Excel macro, you can often find sample code at reputable sites on the internet.

To copy the code from those VBA macros, and add it to one of your workbooks, follow these steps:

1. Copy the sample code that you want to use
2. Open the workbook in which you want to add the code
3. Hold the Alt key, and press the F11 key, to open the Visual Basic Editor
4. Choose Insert | Module
5. Where the cursor is flashing, choose Edit | Paste

To run the code:

1. On the Excel Ribbon, click the View tab
2. At the far right, click Macros
3. Select a macro's name in the list, and click the Run button

Copy Excel VBA Code to a Worksheet Module

Another type of Excel code is Event code, which runs automatically when something specific occurs in the workbook.

For example, if you enter a number in a spreadsheet cell, or select an entry in a cell's drop down list, the worksheet has been changed. This could trigger the Worksheet_Change event.

Worksheet event code is stored on a worksheet module. To add worksheet event code to your worksheet, do the following:

1. Copy the code that you want to use
2. Select the worksheet in which you the code to run
3. Right click on the sheet tab and click View Code, to open the VBA Editor.

   

4. Where the cursor is flashing, choose Edit | Paste

Copy Excel VBA Code to a Workbook Module

Another type of code is Workbook Event code, which should be added to the workbook code module:

1. Copy the code that you want to use
2. Select the workbook in which you want to store the code
3. Hold the Alt key, and press the F11 key, to open the Visual Basic Editor
4. In the Project Explorer, find your workbook, and open the list of Microsoft Excel Objects
5. Right-click on the ThisWorkbook object, and choose View Code
6. Where the cursor is flashing, choose Edit | Paste

Copy Excel VBA Code From a Different Workbook

To see the steps for copying a macro from one workbook to another, in any version of Excel, please watch this short video tutorial. The written instructions are below the video.

Copy Excel VBA Code From a Different Workbook

You may find code in a sample workbook online, and decide to add it to one of your workbooks. You can copy all the code in a module by doing the following:

1. Open both workbooks
2. Hold the Alt key, and press the F11 key, to open the Visual Basic Editor
3. In the Project Explorer, find your workbook, and the workbook with the code that you want to copy. The screenshot at the right, the code is in VBACodeCopy.xls and will be copied to MyForm.xlsm
4. In the workbook with the code, click the + sign to view the list of Modules
5. Click on the module that you want to copy, and drag it over the project where you'd like the copy placed.
6. Release the mouse button, and a copy of the module will appear in the workbook.

To run the code:

1. On the Excel Ribbon, click the View tab
2. At the far right, click Macros
3. Select a macro in the list, and click the Run button

Allow Macros to Run in Your Workbook

To use macros in Excel, you might need to enable them when the file opens. If you are using macros for the first time on your current computer, you might also need to adjust the macro security settings.

Follow the instructions below, to make these changes.

Enable Macros When Opening the File

When you open a workbook that contains macros, you might see a security warning, at the top of the worksheet, above the Formula Bar.

1. Click the Options button.
2. Click Enable This Content, to allow the workbook's macros to run, and click OK.

Check Your Macro Security Settings

If you haven't run macros before, you might need to change your macro security level. (You may have to clear this with your IT department.)

1. On the Ribbon, click the Developer tab, and in the Code group, click Macro Security.
• NOTE: If the Developer tab is not visible, follow the steps here to display it.
2. In the Macro Settings category, under Macro Settings, click Disable all macros with notification
3. Click OK.
4. If you changed the setting, close the workbook, and then reopen it

Run an Excel Macro

After you copy a macro to a regular module, follow the steps below, to run the macro. If the macro does not run, check your macro settings.

To run an Excel macro, follow these steps:

1. Copy the macro code to a regular code module in your file.
2. Then, on the Ribbon's View tab, click the top part of the Macro button, to open the Macro window
3. In the list of macros, click on the macro that you want to run
4. Click the Run button

Create a Macro Shortcut

To make it easier to run your macros, you can create keyboard shortcuts for them.

Later, when you want to run one of your macros, just press the keyboard shortcut keys that you set up.

Create the Macro Shortcut

Follow these steps, to set up the keyboard shortcut for running a macro:

• On the Ribbon, click the Developer tab, and in the Code group, click Macros.
• In the Macro dialog box, click the macro for which you want to create a shortcut- CopyDailyRecords.
• At the bottom right, click the Options button.

• In the Macro Options window, click in the Shortcut Key box
• Press the Shift key, and type a letter to use for your shortcut --R in this example.
  • NOTE: Using the Shift key is not required, but I recommend using it
  • This will help you avoid overwriting one of the built-in Excel shortcuts.

• Click OK to complete the shortcut
• Click Cancel in the Macros window, to close it, without running the macro

Run Macro With Keyboard Shortcut

When you want to run one of your Excel macros, just press the keyboard shortcut keys that you set up.

For example, press Ctrl+Shift+R, to run the macro named CopyDailyRecords, shown in the "Create a Macro Shortcut" example, above.

Add Macro to Quick Access Toolbar

If you use a macro frequently, you can add its icon to the Quick Access Toolbar (QAT). This short video shows the steps, and the written instructions are below the video.

See more tips for using the Quick Access Toolbar with your macros, such as adding QAT buttons that run macros for a specific workbook only.

How to Add a Macro to the QAT

For example, here are the steps to add a macro to the QAT. The macro is named ToggleR1C1 and is stored in the Personal Macro Workbook, which is named Personal.xlsb.

• At the right end of the QAT, click the drop down arrow
• Click More Commands

  

• In the Choose commands from drop down, click Macros

  

• In the list of macros, click the PERSONAL.XLSB!ToggleR1C1 macro
  • PERSONAL.XLSB is the file name for the Personal Macro Workbook
• Click Add, to move it to the Quick Access Toolbar

  

• In the QAT list, click the PERSONAL.XLSB!ToggleR1C1 macro
• Click Modify, and click on an icon for the macro (I use the 8-ball), then click OK

  

• Click OK, to close the Excel Options window.

The macro icon now appears on the QAT, and you can click it to run the macro.

Create a Worksheet Event Macro

To see the steps for creating an Excel Worksheet Change Event macro, watch this short video.

There are written steps on the Contextures Blog, and you can download the sample file used in this video.

Modify Copied Excel VBA Code

If you copy VBA code into your Excel file, you might need to make changes to the object names, or other settings, so that the code works correctly in your file. Here are three things to check, before you try to run the code in your file:

• Check the Sheet Names and Ranges
• Add and Name Objects
• Specify the Target Columns or Rows

Check the Sheet Names and Ranges

If there are sheet names or range references in the code, you can modify them, to match your workbook.

• In the code, look for references to "Worksheets" to "Sheets", and change those to the sheet names in your workbook.
• Also look for "Range" references, such as Range("A1:G100"), and adjust those to match the location of your data.

These references might be at the top of the procedure, in a Set statement:

Set ws = Worksheets("SalesData")

or elsewhere in the code.

If you run the code without modifying the reference, you might see an error message: Run-time error '9': Subscript out of range

To see where the problem is, click the Debug button, and a line of code will be highlighted in yellow.

To stop the code, click the Run menu, then click Reset.

Change the sheet name in the line that was highlighted, save the changes, and try the code again.

Add and Name Objects

If the code refers to objects on the worksheet, be sure to add those objects in your workbook, and use the correct object name in the code.

For example, in the code for the Data Validation Combo Box, you'll need to add a combo box to the worksheet, and name it as TempCombo. Or, if your combo box has a different name, change the code references to match.

Specify the Target Columns or Rows

Some code is designed to run when a cell in a specific row or column is changed.

For example, in the sample code shown below, there is a red dot on the line that says column 3 is the only one where the change will occur.

NOTE: In all of these examples, you could use Row instead of Column, to limit the target to specific rows.

A) In your workbook, if you want the code to run when a cell in column E is changed, you could change the 3 to a 5.

If Target.Column = 5 Then

B) Or, add more columns in the code. For example:

If Target.Column = 3 _
    Or Target.Column = 5 _
    Or Target.Column = 6 Then

C) If you don't want to limit the code to a specific column, you could delete the two rows (If...End If) that are marked with red circles. In that case, the code will run for a change in every column.

D) If you want code to run on any column EXCEPT a specific column, use the Not Equal To operator -- <> -- instead of the equal sign. For example:

If Target.Column <> 3 Then

Get the Sample File

To see examples of workbook modules, worksheet modules and regular code modules, download the Add Code to a Workbook sample file. The zipped file is in xlsm format, and contains macros. Be sure to enable macros when you open the file, if you want to test the macros.

Related Tutorials

Create an Excel UserForm

Macro Troubleshooting Tips

UserForm with ComboBoxes

Edit Your Recorded Macro