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

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

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.

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

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)