Macro to Insert Row and Copy Formula in Excel (2 Methods)

Get FREE Advanced Excel Exercises with Solutions!

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

Macro to Insert Row and Copy Formula in Excel: 2 Ways

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

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

Things to Remember

Conclusion

Macro to Insert Row and Copy Formula in Excel: 2 Ways

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.

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

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.

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.

Download Practice Workbook

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

Macro to Insert Row and Copy Formula.xlsm

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.

6 Comments

AG Apr 21, 2022 at 10:00 PM

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.
- Reply
  Mursalin Aug 30, 2022 at 12:23 PM
  
  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!
Edho Nov 22, 2022 at 6:50 PM

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
- Reply
  Fahim Shahriyar Dipto Nov 24, 2022 at 2:00 PM
  
  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 insert 4 rows in after the active cell. We have put the FormulaColumn number 3 as our formula is existed in column 3. You can change the FormulaColumn number at your preference and the formula will be copied in your inserted rows for the exact formula column.
nathaniel Dec 16, 2022 at 3:37 PM

How can i change the Insert row code to insert the new line below not above the selected
- Reply
  Naimul Hasan Arif Dec 19, 2022 at 5:05 PM
  In general, a new row is inserted above the selected cell when we use the insert command in VBA. In order to insert a new row/line, we need to modify the code. You can use the following code to do so.
```
Sub InsertEntireRow()
Selection.Offset(1, 0).EntireRow.Insert
End Sub
```

Macro to Insert Row and Copy Formula in Excel (2 Methods)

Macro to Insert Row and Copy Formula in Excel: 2 Ways

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

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

Things to Remember

Conclusion

Related Articles

What is ExcelDemy?

Leave a reply Cancel reply