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

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.

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.

Data Set to Develop Macro to Insert Row and Copy Formula in Excel

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.

Data Set with Formula to Develop Macro to Insert Row and Copy Formula in Excel

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.

Saving Workbook to Develop Macro to Insert Row and Copy Formula in Excel

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.

Running Macro to Develop Macro to Insert Row and Copy Formula in Excel

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.

Output to Develop Macro to Insert Row and Copy Formula in Excel

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


Similar Readings


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.

VBA Code to Develop Macro to Insert Row and Copy Formula in Excel

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.

Macro to Insert Row and Copy Formula after a Fixed Interval in Excel

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.


Related Articles

Rifat Hassan

Rifat Hassan

Hello! Welcome to my profile. Here I will be posting articles related to Microsoft Excel. I am a passionate Electrical Engineer holding a Bachelor’s degree in Electrical and Electronic Engineering from Bangladesh University of Engineering and Technology. Besides academic studies, I always love to keep pace with the revolution in technology that the world is rushing towards day by day. I am diligent, career-oriented, and ready to cherish knowledge throughout my life.

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

Leave a reply

ExcelDemy
Logo