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

Get FREE Advanced Excel Exercises with Solutions!

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

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.

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

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

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

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.

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

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

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

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

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

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

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

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

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

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

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.

What is ExcelDemy?

ExcelDemy Learn Excel & Excel Solutions Center provides free Excel tutorials, free support , online Excel training and Excel consultancy services for Excel professionals and businesses. Feel free to contact us with your Excel problems.
Sanjida Ahmed

Hello World! This is Sanjida, an Engineer who is passionate about researching real-world problems and inventing solutions that havenâ€™t been discovered yet. Here, I try to deliver the results with explanations of Excel-related problems, where most of my interpretations will be provided to you in the form of Visual Basic for Applications (VBA) programming language. Being a programmer and a constant solution seeker, made me interested in assisting the world with top-notch innovations and evaluations of data analysis.

We will be happy to hear your thoughts

Advanced Excel Exercises with Solutions PDF