Using VBA to Copy a Formula from the Cell Above in Excel – 10 Methods

This is an overview.

The dataset shows mathematical results in column B. The formula behind the results for each cell is shown in Column D. If you click on the cells in Column B, you will see the operation in the formula bar.

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

Method 1- Embed VBA to a Copy Cell Above with the Formula from the First Column in Excel

To copy the last cell from the first column and paste it below containing the formula:

Steps:

• Press Alt + F11 or go to  Developer -> Visual Basic to open Visual Basic Editor.

• Click Insert -> Module.

• Enter the following code in 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

The code will copy B8 and paste it in B9 with the formula.

• Press F5 or select Run -> Run Sub/UserForm. You can also click the Play icon to run the macro.

This is the output.

Read More: How to Copy Formula in Excel Without Dragging

Method 2 – Using a Macro to Insert User-Defined Copied Rows with the Formula

Steps:

• Press Alt + F11 or go to  Developer -> Visual Basic to open Visual Basic Editor.
• Click Insert -> Module.
• Enter the following code in the code window.

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

• Press F5 or select Run -> Run Sub/UserForm. You can also click the Play icon to run the macro.
• In the input box, enter the row number to insert copied rows. Here, row 6.
• Click OK.

• In the next input, enter the number of rows you want to copy. Here, 3 copies of row 6.
• Click OK.

This is the output.

Row number 6 is copied and pasted with the formula 3 times in rows 7, 8 and 9.

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

Method 3 – Applying VBA to Copy a Cell from Above with the Formula After User-Defined Intervals

You want to copy a cell with the formula and paste it below after an interval. For example, copy B7 and paste it 3 rows below.

Steps:

• Press Alt + F11 or go to  Developer -> Visual Basic to open Visual Basic Editor.
• Click Insert -> Module.
• Enter the following code in the code window.

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

• Run the code.
• In the input box, enter the number of intervals. Here 3.
• Click OK.

This is the output.

B7 is copied with the formula after 3 intervals (rows) in the same column.

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

Method 4 – Applying a Macro Code to Duplicate the Entire Last Row to the Row Below with  the Formula

Steps:

• Press Alt + F11 or go to  Developer -> Visual Basic to open Visual Basic Editor.
• Click Insert -> Module.
• Enter the following code in 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

• Run the code.

This is the output.

If you click a copied cell, it shows the formula in the formula bar.

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

Method 5 – Using a Macro to Copy the Formula from the Cell Above Starting from the Last Cell

The VBA code will start counting from the last cell (B8). When it reaches the cell above the last cell (B7), it inserts a new row. The rest of the cells below are pushed below. The previous B7 becomes B8 and  B8 becomes B9. The code copies B6 with the formula.

Steps:

• Press Alt + F11 or go to  Developer -> Visual Basic to open Visual Basic Editor.
• Click Insert -> Module.
• Enter the following code in 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.

• Run the code.

This is the output.

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

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

Steps:

• Press Alt + F11 or go to  Developer -> Visual Basic to open Visual Basic Editor.
• Click Insert -> Module.
• Enter the following code in 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

• Run the code.

You duplicated all the cells from above in the cells below.  B10 was the active cell.

This is the output.

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

Method 7 – Clearing Existing Data and Copy the Formula from Above with a Macro in Excel

To clear an existing cell and copy the value from the above cell:

Steps:

• Press Alt + F11 or go to  Developer -> Visual Basic to open Visual Basic Editor.
• Click Insert -> Module.
• Enter the following code in 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

B6 is the active cell.

• Run the code.
• In the message box, If you don’t have the correct active, click No and go back to the dataset. If you have the correct cell selected, Click Yes.

• Previous data was cleared from B6 along with the formula and replaced with the value of B5.

Related Content: Excel VBA to Copy Formula with Relative Reference

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

Insert a new cell in B6, copy the cell above: B5 with the formula and paste it into B6.

Steps:

• Press Alt + F11 or go to  Developer -> Visual Basic to open Visual Basic Editor.
• Click Insert -> Module.
• Enter the following code in the code window.

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

• Run the code.

This is the output.

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

Insert 3 cells after B6 and fill them with the value and formula in B6.

Steps:

• Press Alt + F11 or go to  Developer -> Visual Basic to open Visual Basic Editor.
• Click Insert -> Module.
• Enter the following code in 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

• Run the code.

B6 is copied 3 times into the cells below (B7, B8 and B9), containing the formula.

Method 10 – Using VBA to Have Multiple Copies of a Selected Range with Formulas in the Cells Below

Make 10 copies of B8 with the formula in the 10 cells below.

Steps:

• Press Alt + F11 or go to  Developer -> Visual Basic to open Visual Basic Editor.
• Click Insert -> Module.
• Enter the following code in 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

• Run the code.

This is the output.

Download Workbook

Download the free practice Excel workbook.

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