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

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

    • 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!

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

Leave a reply

ExcelDemy
Logo