VBA to Copy Formula from Cell Above in Excel (10 Methods)

When we are dealing with a huge amount of data with various formulas, sometimes we have to copy data with formulas from the cell above in an Excel worksheet. Implementing VBA is the most effective, quickest, and safest method to run any operation in Excel. In this article, we will show you how to copy the cell with the formula from above in Excel with the VBA macro.

VBA to Copy Formula from Cell Above in Excel: 10 Methods

In the following sections, you will learn 10 methods on how you can copy the cell above with the formula with VBA in Excel.

Above is the dataset that this article will follow as our example. In the dataset, we have some mathematical results in column B, which are produced by some mathematical operations. The formula behind the results for each cell is shown in the respective rows in Column D. If you click on the cells in Column B, you will see the operation behind it in the formula bar. Such as, we have shown you in the picture that the operation behind Cell B5 is the mathematical addition of 4 and 2. Cell B6 holds mathematical subtraction. In Cell B7, multiplication is going on. And Cell B8 operates divisions between 4 and 2.

• Cell B5 -> 6 = 4+2
• Cell B6 -> 2 = 4-2
• Cell B7 -> 8 = 4*2
• Cell B8 -> 2 = 4/2

We will see, if we copy any cells from the dataset in the cell below, does it get copied with the formula inside it or not.

1. Embed VBA to Copy Cell Above with Formula from the First Column in Excel

Here, we will show you how to copy the last cell from the first column of the dataset and paste it below the cell with the formula that it has.

Steps:

• In the beginning, press Alt + F11 on your keyboard or go to the tab Developer -> Visual Basic to open Visual Basic Editor.

• Next, in the pop-up code window, from the menu bar, click Insert -> Module.

• Now, copy the following code and paste it into the code window.

Sub CopySingleCellWithFormula()
    Dim iBook As Workbook
    Dim iSheet As Worksheet
    Set iBook = ThisWorkbook
    Set iSheet = Sheets("Single Column")
    With iSheet
        If IsEmpty(Range("B5").Offset(1, 0)) Then
            Range("B5").Copy Range("B5").Offset(1, 0)
        Else
            Range("B5").End(xlDown).Copy Range("B5").End(xlDown).Offset(1, 0)
        End If
    End With
End Sub

Your code is now ready to run.

This code will copy the last cell B8 and paste it in cell B9 with the formula inside.

• Then, press F5 on your keyboard or from the menu bar, select Run -> Run Sub/UserForm. You can also just click on the small Play icon in the sub-menu bar to run the macro.

Look at the following image after the VBA code execution.

Finally, we have successfully copied the cell above (B8) with the formula it had in the cell below (B9). The copied formula for cell B9 is shown in the formula bar.

Read More: How to Copy Formula in Excel Without Dragging

2. Implement Macro to Insert Entire User-Defined Copied Rows with Formula

Now this time, we will let you know how you can insert entire row/s from above with the formula inside by selecting the row number/s and how many times you want that row number to be copied in the cells below.

Steps:

• Same way as before, open Visual Basic Editor from the Developer tab and Insert a Module in the code window.
• Then, in the code window, copy the following code and paste it.

Sub CopyEntireRowWithFormula()
    Dim iSheet As Worksheet
    Dim iRows As Long
    Dim iLastColumn As Long
    Dim iFirstRow As Long
    Set iSheet = ThisWorkbook.Worksheets("Entire Rows")
    iFirstRow = InputBox("Enter Row Number to Insert New Rows from")
    iRows = InputBox("Enter Number of Rows to Insert")
    If iFirstRow = 0 Or iRows = 0 Then Exit Sub
    Debug.Print iFirstRow
    IngA = iSheet.Cells(5, iSheet.Columns.Count).End(xlToLeft).Column
    For i = 1 To iRows
        iSheet.Range("A" & (iFirstRow + i)).EntireRow.Insert
        iSheet.Range("A" & (iFirstRow + i) & ":BB" & (iFirstRow + i)).Formula = iSheet.Range("A" & iFirstRow & ":BB" & iFirstRow).Formula
    Next
End Sub

Your code is now ready to run.

• Next, Run the code as shown in the above section.
• Later, a pop-up input box will appear. It asks you to enter the row number from where you want to insert copied rows. We want to copy the row number 6; hence, we write 6 in the input bar.
• Next, press OK.

• Following that, another pop-up input box will appear. This time, it will ask for the number of rows that you want for your copied row. We want to have 3 copies of row number 6, so, we write 3 in the input bar.
• Again, press OK.

You can see the code execution result in the following image.

Row number 6 is copied and pasted with the formula inside it 3 times in row numbers 7, 8 and 9. And the formula is still in those copied rows, we can see that in the formula bar.

Read More: How to Copy Formula Down Without Incrementing in Excel

3. Insert VBA to Copy the Cell from Above with Formula After User-Defined Intervals

Suppose you want to copy a cell and paste it below after a certain interval. For example, we want to copy cell B7 and paste it after 3 rows below with the formula in the same column.

Let’s see the steps on how we can fulfill that.

Steps:

• As shown above, open Visual Basic Editor from the Developer tab and Insert a Module in the code window.
• Then, in the code window, copy the following code and paste it.

Sub InsertRowsWithFormulaAfterInterval()
iNumber = Int(InputBox("Enter the Interval Numbers"))
Count = 1
For i = iNumber To Selection.Rows.Count - 1 Step iNumber
    Selection.Cells(i + Count, 1).EntireRow.Insert
    Count = Count + 1
Next i
Count = Count - 1
Range(Cells(ActiveCell.Row, ActiveCell.Column), Cells(ActiveCell.Row, ActiveCell.Column)).Copy
Range(Cells(ActiveCell.Row + iNumber, ActiveCell.Column), Cells(ActiveCell.Row + iNumber, ActiveCell.Column)).PasteSpecial xlPasteFormulas
For i = 2 To Count
    Range(Cells(ActiveCell.Row + iNumber + 1, ActiveCell.Column), Cells(ActiveCell.Row + iNumber + 1, ActiveCell.Column)).PasteSpecial xlPasteFormulas
Next i
Application.CutCopyMode = False
End Sub

Your code is now ready to run.

Before running this code, make sure you keep the cell that you want to copy as the active cell. As we want to copy Cell B7, we kept it as the active cell.

• Now, Run the code.
• Later, a pop-up input box will appear asking you to enter the number of intervals that you want from the active cell. We want to copy Cell B7 after 3 rows, so write 3 in the input bar.
• Next, press OK.

Finally, the result after code execution is shown in the following image.

The active cell Cell B7 is now copied with the formula it has after 3 intervals (rows) in the same column. And the formula inside is also copied with it, shown in the formula bar.

Read More: How to Copy Formula and Paste as Text in Excel

4. Apply Macro Code to Duplicate the Entire Last Row in the Row Below with Formula

In this phase, you will learn how to copy the entire last row from the dataset in the row below while the formula will be unharmed.

Steps:

• First, open Visual Basic Editor from the Developer tab and Insert a Module in the code window.
• Second, copy the following code and paste it into the code window.

Sub CopyEntireLastRow()
Dim iRow As Long
iRow = Cells(Rows.Count, "B").End(xlUp).Row
    Rows(iRow).Copy
    Rows(iRow).Offset(1, 0).EntireRow.PasteSpecial Paste:=xlPasteFormats
    Rows(iRow).Offset(1, 0).EntireRow.PasteSpecial Paste:=xlPasteFormulas
    Application.CutCopyMode = False
End Sub

Your code is now ready to run.

• Third, Run this piece of code.

Lastly, we can see in the above image that the entire last row of our dataset is copied below it.

Now, let’s check whether the formula inside Cell B8 is still there or got vanished during the code execution.

As shown in the above picture, when we click on the newly copied cell from above, it shows us in the formula bar that we still have the formula behind the cell.

Read More: How to Copy Formula to Another Sheet in Excel 

5. Macro to Copy Formula from Cell Above Starting from the Last Cell

This part of the article is a bit tricky. The VBA code that we are going to provide you now, will start counting the cell from the last cell (B8) of the dataset. And when it reaches the cell above the last cell (B7), it inserts a new row above that. So, the rest of the cells below are pushed one step below. As a result, the previous Cell B7 becomes Cell B8 and Cell B8 becomes Cell B9. In that newly inserted row (B7), it copies the cell above (B6) with the formula.

Let’s see the VBA code of doing this tricky process.

Steps:

• Firstly, open Visual Basic Editor from the Developer tab and Insert a Module in the code window.
• Copy the following code and paste it into the code window.

Sub CopySelectedCellFromAbove()
    'To go to the last cell
    Range("B5").Select
    Selection.End(xlDown).Select
    LastCell = [B65536].End(xlUp).Offset(-1, 0).Address
    Range(LastCell).Select
    'To enter new line
    Selection.EntireRow.Insert Shift:=xlUp, CopyOrigin:=xlFormatFromLeftOrAbove
    'To copy formula from the cell above
    Dim rng As Range
        For Each rng In Selection
            If (rng.Value = "") Then
            rng.Offset(-1, 0).Copy Destination:=rng
            End If
        Next rng
End Sub

Your code is now ready to run.

• Next, Run this code and look at the image below.

Eventually, we successfully executed the process of copying a certain cell from above with the formula starting from the last cell of the dataset.

Read More: How to Copy and Paste Formulas from One Workbook to Another in Excel

6. Copy All Rows from Above with Formulas with VBA Macro in Excel

This part will describe how to copy all the cells from above and paste those below with the formula inside those cells with VBA code.

Steps:

• Firstly, open Visual Basic Editor from the Developer tab and Insert a Module in the code window.
• Copy the following code and paste it into the code window.

Sub CopyAllRowsFromAboveWithFormulas()
Dim iSource As Range
Dim iTarget As Range
Dim iNum As Long
Set iSource = ActiveSheet.Range("B4:D8") 'Range with formula to copy
iNum = ActiveCell.Row
'Macro will run only if active cell is between columns B and D
If ActiveCell.Column >= 2 And ActiveCell.Column <= 4 Then
        Rows(iNum).Insert Shift:=xlDown, CopyOrigin:=xlFormatFromLeftOrAbove
        Set iTarget = ActiveSheet.Range("B" & iNum & ":D" & iNum) 'Range to paste
        iSource.Copy iTarget
End If
End Sub

Your code is now ready to run.

Make sure to have your active cell in between columns B and D before executing this code, otherwise, the macro won’t run.

• Then, Run this code. And you can see the result in the following image.

After the successful code execution, we have duplicated all the cells from above in the cells below in the same worksheet. Notice that, we had Cell B10 as our active cell.

Not only that we have successfully duplicated all the cells from above, but also the formulas inside those cells are all in one piece.

Related Content: [Fixed] Excel Not Copying Formulas, Only Values (4 Solutions)

7. Clear Existing Data and Copy Formula from Above with Macro in Excel

Suppose you have a value in one of the cells of the dataset where the formula is wrong. Or you want to get rid of one of the cells along with the formula it has because you don’t need it anymore. After that, you want to copy the cell above that deleted cell with the formula that the above cell has. So, all you have to do is, clear the existing cell and copy the value from the above cell with the mathematical calculations.

Let’s see how to perform that with VBA code.

Steps:

• In the beginning, open Visual Basic Editor from the Developer tab and Insert a Module in the code window.
• Then, copy the following code and paste it into the code window.

Sub ClearAndCopyFromAbove()
    Dim Output
    If ActiveCell.Row <> 1 Then 'Will return errors if active cell is in the first row
        Output = MsgBox("Are you sure to copy formulas from above row?", vbYesNo)
        If Output = vbYes Then
            ActiveCell.Offset(-1, 0).EntireRow.Copy
            ActiveCell.EntireRow.PasteSpecial Paste:=xlPasteFormats
            ActiveCell.EntireRow.PasteSpecial Paste:=xlPasteFormulas
            On Error Resume Next
            'Next line will throw errors if no constants, otherwise skip errors
            ActiveCell.EntireRow.Cells.SpecialCells(xlCellTypeConstants).ClearContents
            On Error GoTo 0 'Returns to error capturing line
            ActiveCell.Select 'Removes selection from the entire row
            Application.CutCopyMode = False
        End If
    Else
        MsgBox "Row unavailable from above selection. Nothing to copy from row above."
    End If
End Sub

Your code is now ready to run.

Before running this piece of code, you must select the cell that you want to replace with the cell above to make it the active cell. We wanted to replace Cell B6 with Cell B5 along with the formula, so we keep Cell B6 as our active cell.

• Next, Run this piece of code.
• A pop-up confirmation message box will appear. If you don’t have the correct cell as the active cell, then you can press No and go back to the dataset to make it the active cell. Or, if you have the correct cell selected, then press Yes.

• After confirming with Yes, we have cleared the previous data from Cell B6 along with the previous formula and replaced it with the value of Cell B5.

Finally, to check whether the new formula is really copied with the cell or not, we clicked onto the newly copied Cell B6 and the right formula is shown in the formula bar.

Related Content: Excel VBA to Copy Formula with Relative Reference

8. Embed VBA to Copy Formula from Cell Above and Shift Rest of the Data Down

If you want to insert a new cell which will be the copied version of the cell above and shift the rest of the cells from there down, then follow this section to have the VBA code you need. In our case, we will insert a new cell in Cell B6, copy the cell above (B5) with the formula and paste it into the newly inserted cell.

Steps:

• First, open Visual Basic Editor from the Developer tab and Insert a Module in the code window.
• Copy the following code and paste it into the code window.

Sub CopyAndShiftDown()
    Range("B6").Insert Shift:=xlShiftDown
    Range("B6").FillDown
End Sub

Your code is now ready to run.

• Next, Run this piece of code.

Now, look at the above picture. We have successfully inserted a new cell in Cell B6 and copied Cell B5 along with its formula (shown in the formula bar) in there.

9. VBA to Copy Multiple Cells with Formula from Above and Shift Rest of the Data Down

Previously, we have seen how to insert one cell and fill it with the cell above with the formula. But this time, we learn how to insert multiple cells and fill those with the cell above along with the formula with VBA. We will insert 3 cells after Cell B6 and fill those with the value and formula that lies in cell B6.

Steps:

• In the beginning, open Visual Basic Editor from the Developer tab and Insert a Module in the code window.
• Then, copy the following code and paste it into the code window.

Sub CopyAndShiftDownMultiple()
 Set iRng = Range("B6")
 iRng.Copy
 iRng.Offset(1).Insert Shift:=xlDown
 iRng.Copy
 iRng.Offset(1).Insert Shift:=xlDown
 iRng.Copy
 iRng.Offset(1).Insert Shift:=xlDown
 Application.CutCopyMode = False
End Sub

Your code is now ready to run.

• Now, Run this code and see the result shown in the following image.

Cell B6 is copied 3 times in the cells below (Cells B7, B8 and B9), carrying the exact formula with it.

10. Implement VBA to Have Multiple Copies of Selected Range with Formulas in the Cells Below

Last but not least, this point will show you how to make multiple copies below of a selected cell from above with the formula inside it. We will make 10 copies of Cell B8 with the formula in the 10 cells below.

Steps:

• First, open Visual Basic Editor from the Developer tab and Insert a Module in the code window.
• Second, copy the following code and paste it into the code window.

Sub CopyManyWithFormula()
    Dim iNum As Long
    iNum = 10 'You can customize this number
    Set iRng = Range("B8")
    iRng.Copy
    iRng.Resize(iNum, 1).PasteSpecial (xlFormulas)
End Sub

Your code is now ready to run.

• Next, Run this code. We can check the result in the image below.

As a result of the above procedures, we have 10 copies of Cell B8 below it.

And if we click on any of those cells, we can see that the formula is also copied along with the cell.

Download Workbook

You can download the free practice Excel workbook from here.

Conclusion

In conclusion, this article showed you 10 effective methods on how to copy the cell with the formula from above in Excel with VBA. I hope this article has been very beneficial to you. Feel free to ask if you have any questions regarding the topic.

Related Articles

• VBA to Copy Formula from Cell Above in Excel 
• Excel VBA to Copy Formula with Relative Reference 

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