How to copy Excel macro VBA code to your workbook, from website or sample file. Different types of code, where to paste it. Step-by-step videos, written steps.
Copy Excel VBA Code to a Regular Module
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 that code, and add it to one of your workbooks, follow these steps:
Copy the sample code that you want to use
Open the workbook in which you want to add the code
Hold the Alt key, and press the F11 key, to open the Visual Basic Editor
To run the code:
Copy Excel VBA Code to a Worksheet Module
Worksheet event code is stored on a worksheet module. To add worksheet event code to your worksheet, do the following:
Copy the code that you want to use
Select the worksheet in which you the code to run
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:
Copy the code that you want to use
Select the workbook in which you want to store the code
Hold the Alt key, and press the F11 key, to open the Visual Basic Editor
In the Project Explorer, find your workbook, and open the list of Microsoft Excel Objects
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:
Open both workbooks
Hold the Alt key, and press the F11 key, to open the Visual Basic Editor
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 chúng tôi and will be copied to MyForm.xlsm
Release the mouse button, and a copy of the module will appear in the workbook.
To run the code:
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.
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.)
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:
Copy the macro code to a regular code module in your file.
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
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
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.
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
Last updated: April 14, 2021 1:25 PM