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

## 📂 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.

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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.

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

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

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