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

**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 and User-Defined Intervals**

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

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

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.

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

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

**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 cell, click
**No**and go back to the dataset. If the correct cell is selected, Click**Yes**.

Previous data was cleared from **B6** along with the formula and replaced with the value in **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**

Create 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

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