Rifat Hassan
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.
The present salary is 20% of the starting salary. That means cell D4 in column D holds the formula:
=C4+(C4*20)/100Cell D5 holds the formula:
=C5+(C5*20)/100And 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 SubNote: 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 SubNote: 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.
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 = 3N = 4FormulaColumn = 3R = DataSet.Rows.CountIf R Mod Interval <> 0 ThenEnding = R + (Int(R / Interval) * N)ElseEnding = R + ((Int(R / Interval) - 1) * N)End IfFor i = (Interval + 1) To Ending Step IntervalFor j = 1 To NDataSet.Cells(i, FormulaColumn).EntireRow.InsertNext ji = i + NNext IFor i = 1 To EndingIf DataSet.Cells(i, FormulaColumn) = "" ThenDataSet.Cells(i - 1, FormulaColumn).CopyDataSet.Cells(i, FormulaColumn).PasteSpecial xlPasteFormulasEnd IfNext IApplication.CutCopyMode = FalseEnd SubLook 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.
How can i change the Insert row code to insert the new line below not above the selected
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.