Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 . |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 . . |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 . |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
How to input pictures automatically based on cell input? | Excel Worksheet Functions | |||
input in number form is being multiplied by 1000 when i input. | Excel Discussion (Misc queries) | |||
How do I add input data in the input ranges in drop down boxes. | Excel Discussion (Misc queries) | |||
=SUMIF(Input!H2:H718,AZ19,Input!E2:E685)AND(IF | Excel Worksheet Functions | |||
CODE to select range based on User Input or Value of Input Field | Excel Programming |