In this article, I’ll show you how you can develop a **Macro** to insert a row and copy a formula using **VBA** in Excel. You’ll learn to insert one or more rows before a selected cell, as well as to insert rows after a fixed interval within a data set.

VBA Code to Develop a Macro to Insert Row and Copy Formula in Excel (Quick View)

```
Sub Insert_Rows_Before_Selected_Cell()
Number_of_Rows = Int(InputBox("Enter the Number of Rows to Insert: "))
For i = 1 To Number_of_Rows
Selection.EntireRow.Insert
Next i
Range(Cells(ActiveCell.Row + Number_of_Rows, ActiveCell.Column), Cells(ActiveCell.Row + Number_of_Rows, ActiveCell.Column)).Copy
Range(Cells(ActiveCell.Row, ActiveCell.Column), Cells(ActiveCell.Row, ActiveCell.Column)).PasteSpecial Paste:=xlPasteFormulas
For i = 1 To Number_of_Rows - 1
Range(Cells(ActiveCell.Row + 1, ActiveCell.Column), Cells(ActiveCell.Row + 1, ActiveCell.Column)).PasteSpecial Paste:=xlPasteFormulas
Next i
Application.CutCopyMode = False
End Sub
```

**⧭**** Explanation of the Formula:**

- This code creates a
**Macro**called**Insert_Rows_Before_Selected_Cell**. - It takes one input, the number of rows to be inserted before the selected cell.
- It first inserts the given number of empty rows before the selected cell.
- Then it copies the formula from the selected cell and pastes it inside each of the new rows using the
**xlPasteFormulas**property of**VBA**.

**Table of Contents**hide

**Download Practice Workbook**

Download this practice workbook to exercise while you are reading this article.

**2 Ways to Develop a Macro to Insert Row and Copy Formula in Excel**

Here we’ve got a data set with the **Names, Starting Salaries** and **Present Salaries** of some employees of a company named Jupyter Group.

The present salary is 20% of the starting salary. That means cell **D4** in column **D** holds the formula:

`=C4+(C4*20)/100`

Cell **D5** holds the formula:

`=C5+(C5*20)/100`

And so on.

Our objective today is to develop **Macros** using **Visual Basic Application** (**VBA**) that’ll insert one or more rows between this data set and copy the formula.

**1. Develop a Macro to Insert Row and Copy Formula before a Selected Row**

First of all, we’ll develop a Macro to insert one or more rows below a selected row and copy the formula.

You can use the following **VBA code** for the purpose:

**⧭**** VBA Code:**

```
Sub Insert_Rows_Before_Selected_Cell()
Number_of_Rows = Int(InputBox("Enter the Number of Rows to Insert: "))
For i = 1 To Number_of_Rows
Selection.EntireRow.Insert
Next i
Range(Cells(ActiveCell.Row + Number_of_Rows, ActiveCell.Column), Cells(ActiveCell.Row + Number_of_Rows, ActiveCell.Column)).Copy
Range(Cells(ActiveCell.Row, ActiveCell.Column), Cells(ActiveCell.Row, ActiveCell.Column)).PasteSpecial Paste:=xlPasteFormulas
For i = 1 To Number_of_Rows - 1
Range(Cells(ActiveCell.Row + 1, ActiveCell.Column), Cells(ActiveCell.Row + 1, ActiveCell.Column)).PasteSpecial Paste:=xlPasteFormulas
Next i
Application.CutCopyMode = False
End Sub
```

**Note: **This code creates a **Macro** called **Insert_Rows_Before_Selected_Cell**.

**⧭**** Output:**

First, save the file as **Excel Macro-Enabled Workbook**.

Then select any cell with a formula from your data set and run the **Macro** called **Insert_Rows_Before_Selected_Cell**.

Here I’ve selected cell **D10**.

You’ll get an **Input Box** asking you to enter the number of rows before the selected cell.

For the sake of this example, I’ve entered **3**.

Click **OK**. And you’ll find 3 rows inserted before the selected cell along with copying the formula.

**Read More:** **Excel Macro to Insert Rows (8 Methods)**

**Similar Readings**

**Shortcuts to Insert New Row in Excel (6 Quick Methods)****Excel Fix: Insert Row Option Greyed out (9 Solutions)****Excel Formula to Insert Rows between Data (2 Simple Examples)****How to Insert Multiple Blank Rows in Excel (4 Easy Ways)****How to Insert Rows Automatically in Excel (3 Ways)**

**2. Run a Macro to Enter Row and Copy Formula after a Specific Interval**

This time, we’ll develop a **Macro** that’ll enter rows and copy formulas after a specific interval in a column.

You can use the following **VBA code** for this purpose:

**⧭**** VBA Code:**

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

**Note: **This code creates a **Macro** called **Insert_Rows_After_a_Interval.**

**⧭**** Output:**

First, save the file as **Excel Macro-Enabled Workbook**.

Then select the column with the formulas and run the **Macro** called **Insert_Rows_After_a_Interva**l.

Here I’ve selected the column **Present Salary** (**D4:D13**).

You’ll get an **Input Bok** asking you to enter the interval. Here I’ve entered **3**.

Click **OK**. And you’ll find new rows inserted after an interval of every **3** rows along with the formulas.

**Read More:** **VBA Macro to Insert Row in Excel Based on Criteria (4 Methods)**

**Things to Remember**

Here in this article, we’ve used the **xlPasteFormulas** property of **VBA**. Besides it, there are **11 **more properties in **VBA** that are to paste values.

Click here to know them in detail.

**Conclusion**

Using these methods, you can develop and run a **Macro** to insert rows and copy formulas in Excel. Do you have any questions? Feel free to ask us.

Thank you for such a well explained way to insert rows. I’ve copied and pasted from your Macro to Insert Row and Copy Formula in Excel (2 Methods). I don’t know VBA code. Is there a way to not get the Run-time error’13’: Type Mismatch message? I would like to just exit if I choose to cancel. Thanks for any help you can give.

Dear AG,

Thanks for your comment. To avoid the Run time error in VBA, you need to add an error handling line inside the code. There are different error-handling commands in VBA. To solve the problem, add the below line after the Sub procedure.

On Error Resume Next

You need to add the above line inside the code like the picture below.

To know more about handling errors in Excel VBA, you can check out the article below.

https://www.exceldemy.com/excel-vba-on-error-resume-next/

I hope this reply will solve your problem. Please let us know if you have any other queries.

Thanks!

Hi Mursalin, Thank you for the share.

If I want to insert and copy the formula for the all selection cells, what should I do to modify.

I mean, I want to insert new 3 Row and copy the 5 column cells above or may be without select so auto copying the formula from above line

Hello Edho,

Thanks for your feedback. Though the question is quite unclear to us but we have tried another code for your working purpose.

`Sub X()`

`Set DataSet = Range("B4:D13")`

`Interval = 3`

`N = 4`

`FormulaColumn = 3`

`R = DataSet.Rows.Count`

`If R Mod Interval <> 0 Then`

`Ending = R + (Int(R / Interval) * N)`

`Else`

`Ending = R + ((Int(R / Interval) - 1) * N)`

`End If`

`For i = (Interval + 1) To Ending Step Interval`

`For j = 1 To N`

`DataSet.Cells(i, FormulaColumn).EntireRow.Insert`

`Next j`

`i = i + N`

`Next I`

`For i = 1 To Ending`

`If DataSet.Cells(i, FormulaColumn) = "" Then`

`DataSet.Cells(i - 1, FormulaColumn).Copy`

`DataSet.Cells(i, FormulaColumn).PasteSpecial xlPasteFormulas`

`End If`

`Next I`

`Application.CutCopyMode = False`

`End Sub`

Look at the code image carefully. We have changed the row number,

N=4. It will insert4rows in after the active cell. We have put theFormulaColumnnumber3as our formula is existed in column 3. You can change theFormulaColumnnumber at your preference and the formula will be copied in your inserted rows for the exact formula column.