Input Box
 

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

Input Box
 

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" wrote in message
...
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

Input Box
 

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

-----Original Message-----

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" wrote
in message
...
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



.

Input Box
 

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




-----Original Message-----
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

-----Original Message-----

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" wrote
in message
...
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



.

.

Input Box
 

See revision in response to your later posting of this.

--
Regards,
Tom Ogilvy

"Dav Newbie" wrote in message
...
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

-----Original Message-----

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" wrote
in message
...
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



.