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

Copy Formula with Relative Cell Reference in Excel VBA


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

Data Set to Copy Formula with Relative Cell Reference with VBA in Excel

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

[No need to enter the full destination range, only the first cell 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

Copy Formula with Relative Cell Reference in Excel VBA

⧭ 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

Copy Formula with Relative Cell Reference in Excel VBA

⧭ 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:


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.

Inserting UserForm to Copy Formula with Relative Cell Reference with VBA in Excel

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

Dragging Tools to Copy Formula with Relative Cell Reference with VBA in Excel

⧪ 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

ListBox3 Code to Copy Formula with Relative Cell Reference with VBA in Excel

⧪ 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

TextBox1 Code to Copy Formula with Relative Cell Reference with VBA in Excel

⧪ 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

UserForm Code to Copy Formula with Relative Cell Reference with VBA in Excel

⧪ Step 8: Running the UserForm (The Final Output)

Your UserForm is now ready to use. Run the Macro called Run_UserForm.

Running UserForm to Copy Formula with Relative Cell Reference with VBA in Excel

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

Rifat Hassan

Rifat Hassan

Hello! Welcome to my profile. Here I will be posting articles related to Microsoft Excel. I am a passionate Electrical Engineer holding a Bachelor’s degree in Electrical and Electronic Engineering from the Bangladesh University of Engineering and Technology. Besides academic studies, I always love to keep pace with the revolution in technology that the world is rushing towards day by day. I am diligent, career-oriented, and ready to cherish knowledge throughout my life.

We will be happy to hear your thoughts

Leave a reply

ExcelDemy
Logo