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

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

**Similar Readings**

**How to Copy Formula to Another Sheet in Excel****How to Copy Formula in Excel without Dragging****How to Copy Formula to Entire Column in Excel**

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

**Download Practice Workbook**

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

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

**How to Copy a Formula Across Multiple Rows in Excel****[Fixed] Excel Not Copying Formulas, Only Values****Creating and Copying Formula Using Relative References in Excel****How to Copy Formula in Excel to Change One Cell Reference Only****How to Copy a Formula in Excel Without Changing Cell References****How to Copy Exact Formula in Excel****How to Copy Formula and Paste as Text in Excel****How to Copy and Paste Formulas from One Workbook to Another in Excel****VBA to Copy Formula from Cell Above in Excel****How to Copy Formula Down Without Incrementing in Excel****How to Copy SUM Formula in Excel****How to Copy Formula Down with Shortcut in Excel**

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

Hello JESSPEAR,

Hope you are doing well. In

Discount price Columnwe used formula with relative cell reference that’s whyxlPasteFormulascommand 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 functionto copy the formula with relative reference.