Insert Multiple Rows - variable
 

Hello. Below is my code that inserts a row in multiple sheets based
on where the active cell is. This inserts one row at a time. The
users often need to insert many rows. How can I prompt the user to
ask how many rows they would like to insert, and then have vba use
that input and insert that many rows? Thanks!!


Sheets(Array("Sheet1", "Sheet2", "Sheet3", "Sheet4", )).Select
Sheets("Sheet1").Activate

ActiveCell.Offset(1, 0).EntireRow.Select
Selection.Insert Shift:=xlDown
ActiveCell.Offset(-2, 0).Select
ActiveCell.EntireRow.Copy
ActiveCell.Offset(1, 0).EntireRow.Select
Selection.PasteSpecial Paste:=xlFormulas, Operation:=xlNone,
SkipBlanks:= _
False, Transpose:=False
Application.CutCopyMode = False
Selection.End(xlToLeft).Select

Insert Multiple Rows - variable
 

Sub tst()
x = InputBox("How many rows ")
ActiveCell.Resize(x, 1).EntireRow.Insert
End Sub


"Steve" skrev:

Hello. Below is my code that inserts a row in multiple sheets based
on where the active cell is. This inserts one row at a time. The
users often need to insert many rows. How can I prompt the user to
ask how many rows they would like to insert, and then have vba use
that input and insert that many rows? Thanks!!


Sheets(Array("Sheet1", "Sheet2", "Sheet3", "Sheet4", )).Select
Sheets("Sheet1").Activate

ActiveCell.Offset(1, 0).EntireRow.Select
Selection.Insert Shift:=xlDown
ActiveCell.Offset(-2, 0).Select
ActiveCell.EntireRow.Copy
ActiveCell.Offset(1, 0).EntireRow.Select
Selection.PasteSpecial Paste:=xlFormulas, Operation:=xlNone,
SkipBlanks:= _
False, Transpose:=False
Application.CutCopyMode = False
Selection.End(xlToLeft).Select