9 Examples to Copy Formula from Above Cell Using VBA in Excel

Often on an Excel Worksheet, when working with a large quantity of data and numerous formulas, we need to copy the data containing formulas from above cell. The most efficient, rapid, and secure way to carry out any action in Excel is by using VBA. we’ll demonstrate how to use a VBA macro in Excel to copy required cell-containing formula from the above with 9 easy approaches. If we look at the picture, given below, we can see that the box given inside the red border is the formula copied from the above cell which has been done using the VBA in Excel.

Overview image to copy the formula from the above cell using VBA in Excel


📂  Download Excel File

Download free workbook from here:


Learn to Copy Excel Formula Using VBA from Above Cell with These 9 Examples

While writing this article, “9 Ways to Copy Formula from Cell Above Using Excel VBA”, we’ve considered a dataset that contains approximately 4 columns and 11 rows. In this dataset, we added Performing, Narration, and Formula to make you better understand. But if you want, you can change the entities and put your own values.

Sample dataset containing narration of different formulas

In this dataset, we showed the Performing from cells B6 to B11. The cells C6 to C11 contain the Narration where Addition, Multiplication, Subtraction, and Division are included. The cells D6 to D11 contain descriptions of the Formula.

APPROACH

1. Applying VBA Macro to Copy Above Cells Including Formula from Initial Column

If you want to copy a formula from above cell using Excel VBA, then you can follow this portion. Here we have applied VBA code to copy formula from the initial column as our objective is to copy the formula from the corresponding cells of the first column. It is not a very difficult task. You can easily do this if you follow the below steps.

⬇️⬇️ STEPS ⬇️⬇️

  • Primarily, go to the Excel worksheet.
  • Secondly, press buttons Alt+F11 consecutively on the keyboard.
  • Then you will get a VBA window in front of you.
  • Thirdly, you need to insert a Module.

Inserting the module into the VBA window

  • After that, you will get a module there.
  • Fourthly, write the given code on that module.

Code

Sub FormulaCopyingWithCell()
Dim iBook1 As Workbook
Dim iSheet1 As Worksheet
Set iBook1 = ThisWorkbook
Set iSheet1 = Sheets("Approach 1")
With iSheet1
If IsEmpty(Range("B6").Offset(1, 0)) Then
Range("B6").Copy Range("B6").Offset(1, 0)
Else
Range("B6").End(xlDown).Copy Range("B6").End(xlDown).Offset(1, 0)
End If
End With
End Sub

VBA code to Copy the Cell Above with the Formula from the First Column

  • Afterward, select Run and press Run Sub or userForm or simply F5.

Press the Run button to run the code

  • Finally, you will see that cell B12 has copied the value of the last cell B11.

The result after using the VBA code

Undoubtedly, this technique is very handy to achieve.

📕 Read More: 3 Ways to Copy Formula with Relative Reference Using Excel VBA

APPROACH

2. Using VBA to Put Whole User-Determined Copied Rows Including Formula in Excel

In the previous section, we applied VBA code to copy the formula from the first column. In this part, we are going to show you how to choose row number or numbers, and specify how many repetitions you want those row numbers to be replicated in cells below, in order to insert a full row from above with the formula inside using VBA. Maintain the given steps.

⬇️⬇️ STEPS ⬇️⬇️

  • Initially, you need to go to the Excel worksheet.
  • Secondly, you need to press Alt+F11.
  • Then you will get a VBA window in front of you.
  • Thirdly, you need to insert Module.

Inserting the module into the VBA window

  • Belatedly, you will find a module there.
  • Afterward, write the given code on that module.

Code

Sub FormulaCopyingWithCell()
Dim iSheet1 As Worksheet
Dim iRows1 As Long
Dim iLastColumn1 As Long
Dim iFirstRow1 As Long
Set iSheet1 = ThisWorkbook.Worksheets("Approach 2")
iFirstRow1 = InputBox("Give Row Number")
iRows1 = InputBox("Give Rows Number to Insert")
If iFirstRow1 = 0 Or iRows1 = 0 Then Exit Sub
Debug.Print iFirstRow1
IngA = iSheet1.Cells(6, iSheet1.Columns.Count).End(xlToLeft).Column
For i = 1 To iRows1
iSheet1.Range("A" & (iFirstRow1 + i)).EntireRow.Insert
iSheet1.Range("A" & (iFirstRow1 + i) & ":BB" & (iFirstRow1 + i)).Formula = iSheet1.Range("A" & iFirstRow1 & ":BB" & iFirstRow1).Formula
Next
End Sub

VBA code to Put Whole User-Defined Copied Rows with Formula

  • Don’t forget to select the active cell which you wish to copy.
  • Then press F5 to run code.
  • Now you will get a dialogue box name Give Row Number.
  • Fifthly, insert the cell number of which you wishes to make a copy.
  • Then click OK.

Giving the row number

  • Again you will get a dialogue box name Give Rows Number to Insert.
  • Sixthly, give your desired time to how many times you want the copied cell.
  • Seventhly, tap OK button.

Giving the number of rows to insert

  • Consequently, you will get the required result you wanted.

The result after using the VBA code

Truly, this is a very simple approach to copy formulas from the above cell using VBA in Excel.

📕 Read More: 9 Tricks to Copy Formula in Excel Without Dragging

APPROACH

3. Applying Macros After User-Determined Intervals

Assume that you require to copy the formula of B8 giving 3 or 4 intervals after the last row. So, you need to apply the method which is appropriate for this operation. In this section, we will discuss applying Macros after user-defined intervals to copy the formula from above cell in Excel.

⬇️⬇️ STEPS ⬇️⬇️

  • First, you have to go to the Excel worksheet.
  • Secondly, you have to press Alt+F11.
  • Then you will get a VBA window in front of you.
  • Thirdly, insert Module.
  • Fourthly, you will find a module there.
  • Afterward, paste the given code on that module.

Code

Sub FormulaCopyingWithCell()
iNumber1 = Int(InputBox("Enter the Number of Intervals"))
Count = 1
For i = iNumber1 To Selection.Rows.Count - 1 Step iNumber1
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 + iNumber1, ActiveCell.Column), Cells(ActiveCell.Row + iNumber1, ActiveCell.Column)).PasteSpecial xlPasteFormulas
For i = 2 To Count
Range(Cells(ActiveCell.Row + iNumber1 + 1, ActiveCell.Column), Cells(ActiveCell.Row + iNumber1 + 1, ActiveCell.Column)).PasteSpecial xlPasteFormulas
Next i
Application.CutCopyMode = False
End Sub

VBA code to apply user-defined intervals to copy the formula from the above cell in Excel

  • Before pressing the Run option, select active cells you wish to make a copy.
  • Now press F5 to run code.
  • Now you will get a dialogue box name “Enter the Number of Intervals”.
  • Insert a desired interval you want.
  • Finally, click on the OK button.

Giving the number of intervals

  • Eventually, you will get the result after the number of intervals you inserted.

Result after using the VBA Macro

Lastly, this approach is very handy to copy formula from above cell.

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

APPROACH

4. Adopting Macros to Duplicate Whole Ending Row in Row Under Formulas

Suppose you want to duplicate an entire row from the below row with the formula (from cells B11 to D11). you can do it using the Macros in Excel. You will need a simple code to do this. In this portion, we will adopt a duplication of the last row with formulas to copy formula from the above cells using VBA in Excel.

⬇️⬇️ STEPS ⬇️⬇️

  • Primarily, go to the Excel worksheet.
  • Secondly, press Alt+F11.
  • Then you will get a VBA window in front of you.
  • Thirdly, insert Module.
  • Fourthly, you will find a module there.
  • Belatedly, paste the given code on that module.

Code

Sub CopyingWholeLastRow()
Dim iRow1 As Long
iRow1 = Cells(Rows.Count, "B").End(xlUp).Row
Rows(iRow1).Copy
Rows(iRow1).Offset(1, 0).EntireRow.PasteSpecial Paste:=xlPasteFormats
Rows(iRow1).Offset(1, 0).EntireRow.PasteSpecial Paste:=xlPasteFormulas
Application.CutCopyMode = False
End Sub

VBA code to Duplicate the Whole Last Row in the Row Below with Formula

  • Before pressing the Run option, don’t forget to select the active cell you want to copy.
  • Now press F5 so that you can run the code.
  • Lately, you will see the copied cell you wanted.

The result after using the VBA

  • That’s how you can copy formula from above cell using VBA in Excel.

The result after using the VBA

Indeed, you can simply apply this technique.

📕 Read More: 3 Quick Ways to Copy Formula and Paste as Text in Excel

APPROACH

5. Using VBA to Copy Formulas Beginning from Last Cell

I think this section is a little challenging. VBA code which we’re about to give you will begin counting cells from the dataset’s final cell (B11). Additionally, this inserts an additional row above the previous one (B10) when it gets that cell. The remaining cells are consequently shifted down one step. Because of this, the prior cell B10 is now cell B8, also it is now Cell B12. It duplicates the formula from the cell above (B9) and places it in the newly added row (B10). So we are going to use the VBA to copy formula from the above cell in Excel starting from the last cell.

⬇️⬇️ STEPS ⬇️⬇️

  • Initially, go to the Excel worksheet.
  • Secondly, you need to press Alt+F11.
  • Then you will see a VBA window in front of you.
  • Thirdly, you need to insert Module.
  • Fourthly, you will find a module there.
  • Afterward, paste the given code on that module.

Code

Sub FormulaCopyingWithCell()
'Going last cell
Range("B6").Select
Selection.End(xlDown).Select
LastCell = [B65536].End(xlUp).Offset(-1, 0).Address
Range(LastCell).Select
'Entering new line
Selection.EntireRow.Insert Shift:=xlUp, CopyOrigin:=xlFormatFromLeftOrAbove
'Copying formula from above cell
Dim rng1 As Range
For Each rng1 In Selection
If (rng1.Value = "") Then
rng1.Offset(-1, 0).Copy Destination:=rng1
End If
Next rng1
End Sub

VBA code to Copy Formula Starting from the Last Cell

  • Here we want to copy the value of cell B10.
  • Now you need to press Run.
  • Eventually, you will get the result.

The result after using the VBA to copy the formula from the above cell in Excel

Undoubtedly, this is a very easy way to work on.

📕 Read More: 4 Quick Ways to Copy Formula Down with Shortcut in Excel

APPROACH

6. Applying Macro to Copy Whole Rows with Formulas

There is a technique that you can apply to copy entire rows with formulas in Excel using the VBA Macro. If you apply this process you can copy the formula from the above cell and as a result, you will get the entire rows to be copied. You just need to maintain the following steps.

⬇️⬇️ STEPS ⬇️⬇️

  • First, you have to go to the Excel worksheet.
  • Secondly, press Alt+F11.
  • Then you will find a VBA window in front of you.
  • Thirdly, insert Module.
  • Afterward, you will find a module there.
  • Belatedly, paste the given code on that module.

Code

Sub FormulaCopyingWithCell()
Dim iSource1 As Range
Dim iTarget1 As Range
Dim iNum1 As Long
Set iSource1 = ActiveSheet.Range("B5:D9")
iNum1 = ActiveCell.Row
If ActiveCell.Column >= 2 And ActiveCell.Column <= 4 Then
Rows(iNum1).Insert Shift:=xlDown, CopyOrigin:=xlFormatFromLeftOrAbove
Set iTarget1 = ActiveSheet.Range("B" & iNum1 & ":D" & iNum1)
iSource1.Copy iTarget1
End If
End Sub

VBA code to Copy All Rows with Formulas in Excel

  • Here we want to copy the formula to individual cells.
  • Sixthly, tap F5 to run the code.
  • Consequently, you will get the required result as well.

Result after using VBA to copy the formula from the above cell in Excel

Lastly, we can say that you will not face any difficulty to achieve this approach by copying the formula from above cell using VBA in Excel.

APPROACH

7. Clearing Actual Data and Copying Formula of Above Cells

Let’s say you get a figure in a dataset cell where the given formula is incorrect. Or perhaps you no longer require one of given the cells and the existing formula it contains, therefore you need to get rid of both. The above cell of the eliminated cell should then be copied using the formula from the cell above it. Therefore, all that is required of you is to delete the old cell and then copy the amount from the cell above using mathematical operations.

In this part, we will clear existing data and copy the formula from above cell using VBA in Excel. Follow the given steps.

⬇️⬇️ STEPS ⬇️⬇️

  • Initially, you have to go to the Excel worksheet.
  • Secondly, you need to press Alt+F11.
  • Then you will find a VBA window in front of you.
  • Thirdly, insert Module.
  • Afterward, you will find a module there.
  • Belatedly, paste the given code on that module.

Code

Sub FormulaCopyingWithCell()
Dim Output1
If ActiveCell.Row <> 1 Then
Output1 = MsgBox("Do you want to copy formulas from upper row?", vbYesNo)
If Output1 = vbYes Then
ActiveCell.Offset(-1, 0).EntireRow.Copy
ActiveCell.EntireRow.PasteSpecial Paste:=xlPasteFormats
ActiveCell.EntireRow.PasteSpecial Paste:=xlPasteFormulas
On Error Resume Next
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 is not available. Nothing to copy."
End If
End Sub

VBA code for Clearing Existing Data and Copying Formula from Above

  • Then select the active cell which you want to make a copy.
  • Now tap the F5 key.
  • Consequently, you will find the pop-up saying Do you want to copy formulas from upper row?
  • Click on the OK button.

Pop-up window to copy the formula from the upper row

  • Finally, you will get the desired result as well.

The result after using the VBA code to copy the formula from the above cell in Excel

Truly, you can do this very easily to copy the formula from above cell using VBA in Excel.

APPROACH

8. Adopting VBA Macro to Copy Above Cell Formula and Transfer Remaining Data Over

Follow this part to get the VBA code you ought to add one new cell that will be a copy of the above cell and move the remainder of cells below from there. In this instance, we will create an additional one in cell B7, copy the formula from the cell before (B6), and paste this afterward. Adopting VBA to copy the formula from the cell above and transfer the remaining data down will satisfy our requirements.

⬇️⬇️ STEPS ⬇️⬇️

  • First, go to the Excel worksheet.
  • Secondly, press Alt+F11.
  • Then you will see a VBA window in front of you.
  • Thirdly, you need to insert Module.
  • Afterward, you will see a module there.
  • Belatedly, write the given code on that module.

Code

Sub FormulaCopyingWithCell()
Range("B7").Insert Shift:=xlShiftDown
Range("B7").FillDown
End Sub

VBA code to Copy the Formula from Cell Above and Transfer the Remaining Data Down

  • We want to copy the formula corresponding to B6 cell and paste it into cell B7.
  • Press Run.
  • Eventually, you will get the result as well.

The result after using the VBA to copy the formula from the above cell in Excel

Eventually, this procedure is very easy and handy to work on.

APPROACH

9. Using VBA to Get Multiple Copies Containing Formulas of Selected Range

In this section, we will use VBA to get multiple copies of the selected range. This procedure is very easy and handy. If you follow the given steps, you can easily copy the formula from the above cell using VBA in Excel. Here we will copy the formula to the desired times we want to copy.

⬇️⬇️ STEPS ⬇️⬇️

  • Primarily, you need to go to the Excel worksheet.
  • Secondly, press Alt+F11.
  • Then you will get a VBA window in front of you.
  • Thirdly, you need to insert Module.
  • Fourthly, you will see a module there.
  • Belatedly, write the given code on that module.

Code

Sub CopyingManyFormula()
Dim iNum1 As Long
iNum1 = 12
Set iRng1 = Range("B11")
iRng1.Copy
iRng1.Resize(iNum1, 1).PasteSpecial (xlFormulas)
End Sub

VBA code to Get Multiple Copies of Selected Range

  • Here we want to copy the formula (cell B11) 12 times.
  • Sixthly, you need to press F5 to run the code.
  • You will see the desired result you wanted to have.

The result after using the VBA code

  • Finally, you can follow this procedure to copy the formula from above cell using VBA in Excel.

The result after using the VBA code

Undoubtedly, this technique is very handy to achieve.

📕 Read More: 6 Ways to Copy a Formula Across Multiple Rows in Excel


How to Copy to Clipboard Using VBA in Excel

Excel users typically actively copy (Ctrl+C) and paste (Ctrl+V) values of the data into some other cells. This method of copying and pasting is much more practical thanks to Excel VBA‘s ability to copy cell values to the clipboard. In this portion, we will demonstrate how to copy clipboard using VBA in Excel. You just need to maintain the following steps.

⬇️⬇️ STEPS ⬇️⬇️

  • First, go to the Excel worksheet.
  • Secondly, press Alt+F11.
  • Then you will get a VBA window in front of you.
  • Thirdly, you need to insert Module.

Inserting Module into VBA window

  • Belatedly, you will see a module there.
  • Afterward, write the given code on that module.

Code

Sub CopyClipboardUsingVba()
Worksheets("Copy Clipboard").Range("B11").Copy
End Sub

VBA code to Copy to Clipboard in Excel

  • We want to copy the formula of cell B11 to the clipboard.
  • Now press F5 key.
  • Eventually, you will see the result as well.

The result after using the VBA code

Truly, this approach is very easy to copy clipboard using VBA in Excel.


How to Copy Formula with Relative Reference Using VBA Code in Excel

We frequently need to replicate formulas containing relative cell references from one range to another range when using VBA in Excel. If you are looking for a VBA code to copy a formula containing relative references in Excel then you are in the right place. We are going to show you how you can do this with some easy steps. We will copy the formulas from cell B6 to B11 to other separate cells.

Selecting the range of cells

You just need to follow the given instructions.

⬇️⬇️ STEPS ⬇️⬇️

  • Primarily, you need to go to the Excel worksheet.
  • Secondly, you have to press Alt+F11.
  • Then you will see a VBA window in front of you.
  • Thirdly, you need to insert Module.
  • Fourthly, you will find a module there.
  • Afterward, write the given code on that module.

Code

Sub CopingFormulaRelativeCellReferenceUsingVba()
Worksheets("Relative Reference").Activate
Range("B6:B11").Copy
Range("E6").PasteSpecial Paste:=xlPasteFormulas
End Sub

VBA code

  • Now tap F5 to run the code.
  • Finally, the obtained value will appear to you.

The result after using the VBA code

Indeed, this procedure is very easy and handy to copy a formula containing relative references in Excel using VBA.

📕 Read More: How to Copy a Formula in Excel with Changing Cell References


📄 Important Notes

You should be aware of the following things while performing the processes mentioned above:

🖊️  You have to select the active cell before pressing the Run option to run the code.

🖊️  You need to select the desired cell or cells you want to copy.


📝 Takeaways from This Article

If we summarize the whole article, we have got some points.

📌  Firstly, we applied VBA to copy the cell above with formula from the first column.

📌  Secondly, we used VBA to put whole user-defined copied rows with formula in Excel.

📌  Thirdly, we applied Macros after user-defined intervals.

📌  Fourthly, we adopted Macros to duplicate the whole last row.

📌  Fifthly, we applied VBA to copy last cell.

📌  Sixthly, we used Macro to copy all rows.

📌  Seventhly, we cleared data and copied formulas from above in Excel.

📌  Subsequently, we copied formula from the above cell and transferred the remaining data down in Excel.

📌  Afterward, we used VBA to get multiple copies of the selected range.

📌  Belatedly, we tried to show you the ways to make copy to a clipboard using VBA in Excel.

📌  Finally, we showed you the procedures to copy formula containing relative references in Excel using VBA.


Conclusion

We wish that you would be able to copy formula of above cell using VBA in Excel using this article. As several methods have been shown here, you can easily follow which one is best suited for you. If you have any skepticism, feel free to ask me in the comment. In the end, to learn more about Excel tactics, we recommend you to visit our website www.ExcelDen.com.


Related Article

(Visited 74 times, 1 visits today)
Jonayed

Jonayed

Hello! My name is Jonayed. I've completed my graduation from BUET in Naval Architecture & Marine Engineering. As Excel is very interesting to me, I like to do various types of research regarding Excel. My inquisitive nature drives me to find solutions to different types of problems with Excel.

We will be happy to hear your thoughts

Leave a reply

ExcelDen
Logo