Macro Modification Help

[MM]

Hello,

I have a macro called "insert rows with formulas".


What it does is inserts a new row and at the same time it
copies all the formulas from the row above into the newly
inserted row(s).

The issue is it only inserts one row at a time and usually
there needs to be several rows entered and copied at once
so the user has to keep clicking the macro button for each
row to be inserted. How can I modify this code to open an
input box, have it ask how many rows to enter, and have
the user enter a number for the rows to be entered?

Here is the code:

Sub insertrowswithformulas()

With ActiveCell
..EntireRow.Insert
Range(Cells(.Row - 2, "I"), Cells(.Row - 2, "AP")).Copy _
Cells(.Row - 1, "I")
Range(Cells(.Row - 2, "B"), Cells(.Row - 2, "G")).Copy _
Cells(.Row - 1, "B")
End With
End Sub

 

[merjet]

You only need to use an InputBox and put the code you have
inside a loop. In what follows the new lines are marked as such.

Sub insertrowswithformulas()
Dim iCt As Integer 'new
Dim iCt2 As Integer 'new
iCt2 = InputBox("Enter number of rows to duplicate.") 'new
For iCt = 1 To iCt2 'new
With ActiveCell
.EntireRow.Insert
Range(Cells(.Row - 2, "I"), Cells(.Row - 2, "AP")).Copy _
Cells(.Row - 1, "I")
Range(Cells(.Row - 2, "B"), Cells(.Row - 2, "G")).Copy _
Cells(.Row - 1, "B")
End With
Next iCt 'new
End Sub

HTH,
Merjet