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** 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
```

**Download Practice Workbook**

Download this practice workbook to exercise while you are reading this article.

**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).

**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 run the code, it copied the formulas from the range **D4:D13** to of **Sheet1** to the range **E4:E13** of **Sheet1.**

**Related Content: How to Copy a Formula in Excel with Changing Cell References**

**Similar Readings:**

**How to Copy Formula to Another Sheet in Excel (4 Ways)****Copy a Formula Down the Column in Excel(7 Methods)****How to Copy Formula in Excel without Dragging (10 Ways)****Copy Formula to Entire Column in Excel (7 Ways)****3 Quick Ways to Copy Down Excel Formula without Incrementing**

**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
UserForm1.ListBox2.AddItem Rng.Cells(1, i)
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
UserForm1.ListBox1.AddItem Sheets(i).Name
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
UserForm1.ListBox3.AddItem Sheets(i).Name
Next i
Load UserForm1
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: Copy Formula in Excel by Changing Only One Cell Reference**

**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. And don’t forget to visit our site **ExcelDemy** for more posts and updates.