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

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.

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

Read More: How to Copy Formula in Excel Without Dragging (10 Ways)

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 Formula Down Without Incrementing in Excel
• How to Copy Formula and Paste as Text in Excel
• How to Copy Formula to Another Sheet in Excel
• How to Copy and Paste Formulas from One Workbook to Another in Excel
• [Fixed] Excel Not Copying Formulas, Only Values
• VBA to Copy Formula from Cell Above in Excel

<< Go Back to Copy Formula in Excel | Excel Formulas | Learn Excel