Copy formulas when inserting a row but with user input on the amount of row to be inserted-CodePudding

I have the following code which works great, but it will only add one new row! Does anybody know how i can add some code to this existing code so that when the function is run it asks the user to enter the amount of rows they wish to have created.

Thank you for taking the time to look at this.

With ActiveCell.EntireRow
  .Copy
  .Offset(1).Insert Shift:=xlDown, CopyOrigin:=xlFormatFromLeftOrAbove
   On Error Resume Next
  .Offset(1).SpecialCells(xlCellTypeConstants).Value = ""
  Application.CutCopyMode = False
  On Error GoTo 0
End With

CodePudding user response：

Try this one:

Sub AddRows()

Dim howMany As Long
howMany = InputBox("How many rows?")

Dim i As Long
For i = 1 To howMany
    ActiveCell.EntireRow.Insert
Next

End Sub

EDIT:
To keep the formulas, then just put your original code inside the for loop.

Sub AddRows()

Dim howMany As Long
howMany = InputBox("How many rows?")

Dim i As Long
For i = 1 To howMany
    With ActiveCell.EntireRow
      .Copy
      .Offset(1).Insert Shift:=xlDown, CopyOrigin:=xlFormatFromLeftOrAbove
       On Error Resume Next
      .Offset(1).SpecialCells(xlCellTypeConstants).Value = ""
      Application.CutCopyMode = False
      On Error GoTo 0
    End With
Next

End Sub