# Excel VBA to Copy Formula with Relative Reference (A Detailed Analysis)

Get FREE Advanced Excel Exercises with Solutions!

While working with VBA in Excel, we often need to copy formula (s) with relative cell reference (s) from one range to another range. In this article, I’ll show you how you can formula (s) with relative cell reference (s) with VBA in Excel. You’ll learn to copy formula (s) through step-by-step analysis with examples involving a Macro and a UserForm.

Excel VBA to Copy Formula with Relative Cell Reference (Quick View)

``````Sub Copy_Formula_with_Relative_Cell_References()
Worksheets("Sheet1").Activate
Range("D4:D13").Copy
Range("E4").PasteSpecial Paste:=xlPasteFormulas
End Sub`````` ## How to Copy Formula with Relative Cell Reference with VBA in Excel (Step by Step Analysis)

Without further delay, let’s move to our main discussion. Here we’ve got a data set with the names of some books, their original prices, and discount prices. The original prices (D4:D13) contain formulas with relative cell references.

`=(C4/100)*75` Now we’ll see how we can copy these formulas to the next column (E4:E13) with VBA. I am showing you the step-by-step procedure.

⧪ Step 1: Activating the Source Worksheet

The 1st step that we need to accomplish is to activate the source worksheet. Here the source worksheet is Sheet1. The line of code will be:

``Worksheets("Sheet1").Activate``

This step isn’t mandatory if the source worksheet is already active before running the code.

⧪ Step 2: Copying the Desired Range from the Activated Worksheet

Then we have to copy the desired range from the active worksheet. Here we’ll copy the range D4:D13. The line of code will be:

``ActiveSheet.Range("D4:D13").Copy``

⧪ Step 3: Activating the Destination Worksheet

Next, we have to activate the destination worksheet. Here it’s also Sheet1.

The line of code will be:

``Worksheets("Sheet1").Activate``

Again, this method isn’t mandatory if the source worksheet is already open. That is, the source worksheet and the destination worksheet is the same.

As the source worksheet and the destination worksheet is the same here (Sheet1), we’ll skip this line.

⧪ Step 4: Pasting the Formula with the xlPasteFormulas Property of the VBA PasteSpecial Method

Finally, we have to paste the formulas with the xlPasteFormulas property of VBA in the destination range (E4:E13 here).

[No need to enter the full destination range, only the first cell E4 will do.]

The line of code will be:

``Range("E4").PasteSpecial Paste:=xlPasteFormulas``

⧪ Step 5 (Optional): Turning off the CutCopyMode

This is optional. If you want, you may turn the CutCopyMode off after pasting the values.

``Application.CutCopyMode = False``

So the complete VBA code will be:

⧭ VBA Code:

``````Sub Copy_Formula_with_Relative_Cell_References()
Worksheets("Sheet1").Activate
Range("D4:D13").Copy
Range("E4").PasteSpecial Paste:=xlPasteFormulas
End Sub`````` ⧭ Output:

Run the code. It’ll copy the formulas with the relative cell references from the range D4:D13 to E4:E13. ## Examples Involving Copying Formula with Relative Cell Reference in Excel VBA (Macro and UserForm)

We’ve learned how to copy formulas with relative cell references with VBA. Now let’s explore a few examples involving this.

### Example 1: Developing a Macro to Copy Formula with Relative Cell Reference with VBA in Excel

First of all, we’ll develop a Macro to copy formulas with relative cell references. Actually, we’ve developed it already.

The VBA code to copy the formulas of the range D4:D13 of Sheet1 to the range E4:E13 of Sheet1 was:

⧭ VBA Code:

``````Sub Copy_Formula_with_Relative_Cell_References()
Worksheets("Sheet1").Activate
Range("D4:D13").Copy
Range("E4").PasteSpecial Paste:=xlPasteFormulas
Application.CutCopyMode = False
End Sub`````` ⧭ Output:

When we ran the code, it copied the formulas from the range D4:D13 to Sheet1 to the range E4:E13 of Sheet1. ### Example 2: Developing a UserForm to Copy Formula with Relative Cell Reference in VBA in Excel

Now, we’ll develop a UserForm to copy only the values to a destination range with VBA. I’m showing you the step-by-step procedure.

⧪ Step 1: Inserting a New UserForm

First of all, go to the Insert > UserForm button of the Visual Basic Editor to insert a new UserForm. ⧪ Step 2: Dragging the Necessary Tools

A UserForm called UserForm1 will be created with a Toolbox called Control. Drag 6 Labels (Label1, Label2, Label3, Label4, Label5, and Label6), 3 ListBoxes (ListBox1, ListBox2, and ListBox3), 1 TextBox (TextBox1), and 1 CommandButton (CommandButton1) to the UserForm.

Change the displays of the Labels to Copy From, Worksheet, Columns, Paste To, Worksheet, and Cell respectively (as shown in the image).

Also, change the display of the CommandButton1 to OK. ⧪ Step 3: Inserting Code for ListBox1

Double click on ListBox1. A Private Subprocedure called ListBox1_Click will open. Insert the following code there.

``````Private Sub ListBox1_Click()

For i = 0 To UserForm1.ListBox1.ListCount - 1
If UserForm1.ListBox1.Selected(i) = True Then
Worksheets(UserForm1.ListBox1.List(i)).Activate
Exit For
End If
Next i

ActiveSheet.UsedRange.Select

UserForm1.ListBox2.Clear

Set Rng = ActiveSheet.UsedRange

For i = 1 To Rng.Columns.Count
If Rng.Cells(1, i) <> "" Then
End If
Next i

End Sub`````` ⧪ Step 4: Inserting Code for ListBox3

Then double-click on ListBox3. Another Private Subprocedure called ListBox3_Click will open. Insert the following code there.

``````Private Sub ListBox3_Click()

On Error GoTo LB3

For i = 0 To UserForm1.ListBox3.ListCount - 1
If UserForm1.ListBox3.Selected(i) = True Then
Worksheets(UserForm1.ListBox3.List(i)).Activate
If UserForm1.ListBox1.Selected(i) = False Then
If UserForm1.TextBox1.Text = "" Then
Range("A1").Select
Else
Range(UserForm1.TextBox1.Text).Select
End If
Exit For
End If
End If
Next i

Exit Sub

LB3:
Range("A1").Select

End Sub`````` ⧪ Step 5: Inserting Code for TextBox1

Again double click on TextBox1. A Private Subprocedure called TextBox1_Change will open. Insert this code there:

``````Private Sub TextBox1_Change()

On Error GoTo TB1

For i = 0 To UserForm1.ListBox3.ListCount - 1
If UserForm1.ListBox3.Selected(i) = True Then
Worksheets(UserForm1.ListBox3.List(i)).Activate
Exit For
End If
Next i

ActiveSheet.Range(UserForm1.TextBox1.Text).Select

Exit Sub

TB1:
x = 21

End Sub`````` ⧪ Step 6: Inserting Code for CommandButton1

Add this code for the CommandButton1 (CommandButton1_Click).

``````Private Sub CommandButton1_Click()

For i = 0 To UserForm1.ListBox1.ListCount - 1
If UserForm1.ListBox1.Selected(i) = True Then
Source_Sheet = Worksheets(UserForm1.ListBox1.List(i)).Name
Exit For
End If
Next i

For i = 0 To UserForm1.ListBox3.ListCount - 1
If UserForm1.ListBox3.Selected(i) = True Then
Destination_Sheet = Worksheets(UserForm1.ListBox3.List(i)).Name
Exit For
End If
Next i

Count = 1

For i = 0 To UserForm1.ListBox2.ListCount - 1
If UserForm1.ListBox2.Selected(i) = True Then
Worksheets(Source_Sheet).Activate
For j = 1 To ActiveSheet.UsedRange.Columns.Count
If ActiveSheet.UsedRange.Cells(1, j) = UserForm1.ListBox2.List(i) Then
ActiveSheet.UsedRange.Range(Cells(1, j), Cells(ActiveSheet.UsedRange.Rows.Count, j)).Copy
Worksheets(Destination_Sheet).Activate
Range(UserForm1.TextBox1.Text).Cells(1, Count).PasteSpecial Paste:=xlPasteFormulas
Count = Count + 1
Exit For
End If
Next j
End If
Next i

Application.CutCopyMode = False

End Sub`````` ⧪ Step 7: Inserting Code for Running the UserForm

Finally, insert a new Module and add this code for running the UserForm.

``````Sub Run_UserForm()

UserForm1.Caption = "Copy Formulas with Relative References"

UserForm1.ListBox1.ListStyle = fmListStyleOption
UserForm1.ListBox1.BorderStyle = fmBorderStyleSingle

For i = 1 To Sheets.Count
Next i

For i = 0 To UserForm1.ListBox1.ListCount - 1
If UserForm1.ListBox1.List(i) = ActiveSheet.Name Then
UserForm1.ListBox1.Selected(i) = True
End If
Next i

UserForm1.ListBox2.ListStyle = fmListStyleOption
UserForm1.ListBox2.BorderStyle = fmBorderStyleSingle

UserForm1.ListBox2.MultiSelect = fmMultiSelectMulti
UserForm1.ListBox3.ListStyle = fmListStyleOption
UserForm1.ListBox3.BorderStyle = fmBorderStyleSingle

For i = 1 To Sheets.Count
Next i

UserForm1.Show

End Sub`````` ⧪ Step 8: Running the UserForm (The Final Output)

Your UserForm is now ready to use. Run the Macro called Run_UserForm. The UserForm will load in the worksheet with the title Copy Formulas with Relative References. Enter the required inputs there.

I want to copy the columns called Discount Price of Sheet1 to cell E4 of Sheet1.

So I have inserted inputs like this. Then click on the button OK. The formulas of the column Discount Price will be copied to the destination range. Related Content: How to Copy a Formula in Excel with Changing Cell References

## Things to Remember

Here we’ve used the xlPasteFormulas property of the PasteSpecial method of VBA. Other than the xlPasteFormulas property, there are 11 more properties of the PasteSpecial method. Click to know them in detail.

## Conclusion

So this is these are the ways to copy formulas with relative cell references with VBA in Excel. Hope the examples made everything pretty clear for you. Still, if you have any questions, feel free to ask us.

## Related Articles Rifat Hassan

Hello! Welcome to my profile. Here I will be posting articles related to Microsoft Excel. I am a passionate Electrical Engineer holding a Bachelor’s degree in Electrical and Electronic Engineering from the Bangladesh University of Engineering and Technology. Besides academic studies, I always love to keep pace with the revolution in technology that the world is rushing towards day by day. I am diligent, career-oriented, and ready to cherish knowledge throughout my life.

1. Reply It looks like you are using absolute cell references… Relative cell references use the “.Offset(,)” property, don’t they?

• Reply Afrina Nafisa Sep 18, 2023 at 6:09 PM

Hello JESSPEAR,

Hope you are doing well. In Discount price Column we used formula with relative cell reference that’s why xlPasteFormulas command copying the formula of that column with same cell reference. In Column E you will get the result with relative cell reference.

However, you can also use the OFFSET function to copy the formula with relative reference. ``````Sub Copy_Formula_with_Relative_Cell_References()
Worksheets("Sheet1").Activate
Range("D4:D13").Copy
Range("E4").PasteSpecial Paste:=xlPasteFormulas
Application.CutCopyMode = False
End Sub
`````` Advanced Excel Exercises with Solutions PDF  