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

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