Input Box

[Dav Newbie]

Hi,

The macro below is attached to a command button that when
clicked copies the row and formulas down from the row
above. I am finding that I need to insert multiple rows
and copy those formulas...Can someone please help me
modify the code so that an input box will pop up and ask
me how many rows to insert then excute the macro with the
number entered. So If I say 2 then it inserts two rows etc?

Thanx much

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

 

[Bob Phillips]

nRows = Inputbox("How many rows")
activecell.resize(nrows,1).entirerow.insert

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

 

[Dav Newbie]

Hmm...I applied the modification this way:

Sub insertrowswithformulas()
nrows = InputBox("How many rows")
ActiveCell.Resize(nrows, 1).EntireRow.Insert
With ActiveCell
..EntireRow.Insert
Range(Cells(.Row - 2, "I"), Cells(.Row - 2, "AL")).Copy _
Cells(.Row - 1, "I")
Range(Cells(.Row - 2, "B"), Cells(.Row - 2, "G")).Copy _
Cells(.Row - 1, "B")
End With
End Sub

But here is what it does...when I click the commabd button
with the next row highlighted for insert, the macro now
inserts a blank row and cpoies down the formulas from only
one row. So if I say 2 rows, it inserts two blank rows and
one row with the formulas copied. What I would like is to
have it insert however many rows stated and at the same
time, copy the formulas in the above row down to the next
row it inserts...Just as it did before the modification
only with it asking how many...

Thanx...Again for the help...

D

 

[LarryP]

Try it this way:
(1) Select any cell in your starting row (the one you want
copied)
(2) Launch your macro to
(a) save the associated Row number (ActiveCell.Row) in
a variable
(b) get/save your row count (HowMany) via an InputBox
(c)add the requisite number of new rows with code
something like this:

Rows(ActiveCell.Row & ":" & ActiveCell.Row +
HowMany).Select
Selection.Insert Shift:=xlDown

(d)re-select your entire starting row (using the saved row
number from Step a) and copy it
(e)Select all the new rows (original row + 1 to original
row + HowMany) and paste the formulas

 

[Tom Ogilvy]

See revision in response to your later posting of this.