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

**Table of Contents**hide

**Download Workbook**

You can download the free practice Excel workbook from here.

**10 Methods with VBA to Copy Formula from Cell Above in Excel**

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 a Formula Across Multiple Rows in Excel (5 Ways)**

**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 in Excel (6 Quick Methods)**

**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 fulfil 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: Copy and Paste Formulas from One Workbook to Another 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 a Formula Down the Column in Excel(7 Methods)**

**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. - Secondly,
**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: Excel VBA to Copy Formula with Relative Reference (A Detailed Analysis)**

**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. - Secondly,
**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:** **How to Copy Formula in Excel without Dragging (10 Ways)**

**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: How to Copy Formula to Entire Column in Excel (7 Ways)**

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

**Read More:** **Shortcut to Copy Formula Down in Excel (7 Ways)**

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

**Read More: How to Copy Excel Sheet with Formulas to Another Workbook (5 Ways)**

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