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").ActivateRange("D4:D13").CopyRange("E4").PasteSpecial Paste:=xlPasteFormulasEnd Sub`

**Table of Contents** hide

Download Practice Workbook

How to Copy Formula with Relative Cell Reference with VBA in Excel (Step by Step Analysis)

Examples Involving Copying Formula with Relative Cell Reference in Excel VBA (Macro and UserForm)

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

Example 2: Developing a UserForm to Copy Formula with Relative Cell Reference in VBA in Excel

Things to Remember

Conclusion

Related Articles

**Download Practice Workbook**

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

**Copy Formula with Relative Reference.xlsm**

**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").ActivateRange("D4:D13").CopyRange("E4").PasteSpecial Paste:=xlPasteFormulasEnd 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").ActivateRange("D4:D13").CopyRange("E4").PasteSpecial Paste:=xlPasteFormulasApplication.CutCopyMode = FalseEnd 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 IfNext iActiveSheet.UsedRange.SelectUserForm1.ListBox2.ClearSet Rng = ActiveSheet.UsedRangeFor i = 1 To Rng.Columns.Count If Rng.Cells(1, i) <> "" Then UserForm1.ListBox2.AddItem Rng.Cells(1, i) End IfNext iEnd 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 LB3For 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 IfNext iExit SubLB3: Range("A1").SelectEnd 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 TB1For i = 0 To UserForm1.ListBox3.ListCount - 1 If UserForm1.ListBox3.Selected(i) = True Then Worksheets(UserForm1.ListBox3.List(i)).Activate Exit For End IfNext iActiveSheet.Range(UserForm1.TextBox1.Text).SelectExit SubTB1: x = 21End 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 IfNext iFor 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 IfNext iCount = 1For 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 IfNext iApplication.CutCopyMode = FalseEnd 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 = fmListStyleOptionUserForm1.ListBox1.BorderStyle = fmBorderStyleSingleFor i = 1 To Sheets.Count UserForm1.ListBox1.AddItem Sheets(i).NameNext iFor i = 0 To UserForm1.ListBox1.ListCount - 1 If UserForm1.ListBox1.List(i) = ActiveSheet.Name Then UserForm1.ListBox1.Selected(i) = True End IfNext iUserForm1.ListBox2.ListStyle = fmListStyleOptionUserForm1.ListBox2.BorderStyle = fmBorderStyleSingleUserForm1.ListBox2.MultiSelect = fmMultiSelectMultiUserForm1.ListBox3.ListStyle = fmListStyleOptionUserForm1.ListBox3.BorderStyle = fmBorderStyleSingleFor i = 1 To Sheets.Count UserForm1.ListBox3.AddItem Sheets(i).NameNext iLoad UserForm1UserForm1.ShowEnd 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.

**Related Articles**

- How to Copy a Formula Across Multiple Rows in Excel (5 Ways)
- Copy Exact Formula in Excel (13 Methods)
- How to Copy Formula and Paste as Text in Excel (2 Ways)
- Copy and Paste Formulas from One Workbook to Another in Excel
- VBA to Copy Formula from Cell Above in Excel (10 Methods)