Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Excel Macros
I have created a Template in Excel to be used for capital budgeting purposes.
One area of the spreadsheet has a series of preformatted rows for users to input "Assumptions" supporting their analysis contained in the spreadsheet. I have added a button on the spreadsheet that when clicked is suppose to add additional preformatted "Assumptions" rows below those in the original template if necessary. I have created a macro to do said task and assigned it to the button. My problem is that the macro I created always inputs the new row above the row that I originally selected when I recorded the macro instead of following the last added row. Is there any way that I can record a macro that will only add new rows below those already created either in the original template or subsequently by running the macro. Your assistance would be greatly appreciated. |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Excel Macros
What's the code that you're using?
-- Brevity is the soul of wit. "Brock" wrote: I have created a Template in Excel to be used for capital budgeting purposes. One area of the spreadsheet has a series of preformatted rows for users to input "Assumptions" supporting their analysis contained in the spreadsheet. I have added a button on the spreadsheet that when clicked is suppose to add additional preformatted "Assumptions" rows below those in the original template if necessary. I have created a macro to do said task and assigned it to the button. My problem is that the macro I created always inputs the new row above the row that I originally selected when I recorded the macro instead of following the last added row. Is there any way that I can record a macro that will only add new rows below those already created either in the original template or subsequently by running the macro. Your assistance would be greatly appreciated. |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Excel Macros
Dave,
Not sure. I'm certainly no expert when it comes to marco writing. All I've done is selected "record Macro", executed a numer of steps in excel - i.e. insert row, copy down, type formula to auto number one of the cells in the inserted row etc. and clicked stop macro and assigned it to the button I have created. The first time I hit the button the marco works properly. For each additional time I hit the button the marco inserts a row above the first one that it inserted which does not work for me because each additional row added in suppose to be numbered. "Dave F" wrote: What's the code that you're using? -- Brevity is the soul of wit. "Brock" wrote: I have created a Template in Excel to be used for capital budgeting purposes. One area of the spreadsheet has a series of preformatted rows for users to input "Assumptions" supporting their analysis contained in the spreadsheet. I have added a button on the spreadsheet that when clicked is suppose to add additional preformatted "Assumptions" rows below those in the original template if necessary. I have created a macro to do said task and assigned it to the button. My problem is that the macro I created always inputs the new row above the row that I originally selected when I recorded the macro instead of following the last added row. Is there any way that I can record a macro that will only add new rows below those already created either in the original template or subsequently by running the macro. Your assistance would be greatly appreciated. |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
Excel Macros
Brock
ActiveSheet.Cells(Rows.Count, 1).End(xlUp) _ .Offset(1, 0).Resize(4, 1).EntireRow.Insert The formatting of the 4 inserted rows I will leave to you. Gord Dibben MS Excel MVP On Tue, 5 Sep 2006 13:19:03 -0700, Brock wrote: I have created a Template in Excel to be used for capital budgeting purposes. One area of the spreadsheet has a series of preformatted rows for users to input "Assumptions" supporting their analysis contained in the spreadsheet. I have added a button on the spreadsheet that when clicked is suppose to add additional preformatted "Assumptions" rows below those in the original template if necessary. I have created a macro to do said task and assigned it to the button. My problem is that the macro I created always inputs the new row above the row that I originally selected when I recorded the macro instead of following the last added row. Is there any way that I can record a macro that will only add new rows below those already created either in the original template or subsequently by running the macro. Your assistance would be greatly appreciated. |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
Excel Macros
OK, to view the code, right-click the tab in which the macro was recorded,
and select view code. You should see a window with a bunch of programming code (which is what Excel creates when you record the macro.) Copy and paste that into this post so that people can see what your code is. Hard to diagnose without seeing the code. Dave -- Brevity is the soul of wit. "Brock" wrote: Dave, Not sure. I'm certainly no expert when it comes to marco writing. All I've done is selected "record Macro", executed a numer of steps in excel - i.e. insert row, copy down, type formula to auto number one of the cells in the inserted row etc. and clicked stop macro and assigned it to the button I have created. The first time I hit the button the marco works properly. For each additional time I hit the button the marco inserts a row above the first one that it inserted which does not work for me because each additional row added in suppose to be numbered. "Dave F" wrote: What's the code that you're using? -- Brevity is the soul of wit. "Brock" wrote: I have created a Template in Excel to be used for capital budgeting purposes. One area of the spreadsheet has a series of preformatted rows for users to input "Assumptions" supporting their analysis contained in the spreadsheet. I have added a button on the spreadsheet that when clicked is suppose to add additional preformatted "Assumptions" rows below those in the original template if necessary. I have created a macro to do said task and assigned it to the button. My problem is that the macro I created always inputs the new row above the row that I originally selected when I recorded the macro instead of following the last added row. Is there any way that I can record a macro that will only add new rows below those already created either in the original template or subsequently by running the macro. Your assistance would be greatly appreciated. |
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
Excel Macros
He says in there that he recorded the macro, so I suspect it looks something
like this: Rows("9:9").Select Selection.Insert Shift:=xlDown Possibly with more Selection.Insert Shift:=xlDown statements following it. Problem is that he doesn't know if the button has ever been used, or if it has been used, then how many times has it been used? i.e. how many new rows have been added. Easiest way to deal with it would probably be by giving a cell in the row that is to remain at the bottom of the list (or bottom of where new rows are inserted) a Name and use that to find it. Lets say the row that is where you want to insert initially is row 10, you could name cell A10 something like "NewRowsEnd" or such (select cell A10, type NewRowsEnd into the Name Box - that's where you see A10 displayed right above the column indicator for column A - and press the [Enter] key). Then the code could read Range("NewRowsEnd").Select Selection.Insert Shift:=xlDown and whatever else is in the macro now and it would always go to the end of that section to begin inserting new rows. There are other ways, but that's one that seems easiest to implement without seeing the spreadsheet itself. "Dave F" wrote: What's the code that you're using? -- Brevity is the soul of wit. "Brock" wrote: I have created a Template in Excel to be used for capital budgeting purposes. One area of the spreadsheet has a series of preformatted rows for users to input "Assumptions" supporting their analysis contained in the spreadsheet. I have added a button on the spreadsheet that when clicked is suppose to add additional preformatted "Assumptions" rows below those in the original template if necessary. I have created a macro to do said task and assigned it to the button. My problem is that the macro I created always inputs the new row above the row that I originally selected when I recorded the macro instead of following the last added row. Is there any way that I can record a macro that will only add new rows below those already created either in the original template or subsequently by running the macro. Your assistance would be greatly appreciated. |
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
Excel Macros
Sub Macro6()
' ' Macro6 Macro ' Macro recorded 05/09/2006 by brock ' ' Rows("25:25").Select Selection.Insert Shift:=xlDown Range("C24:R24").Select Selection.AutoFill Destination:=Range("C24:R25"), Type:=xlFillDefault Range("C24:R25").Select End Sub "Dave F" wrote: OK, to view the code, right-click the tab in which the macro was recorded, and select view code. You should see a window with a bunch of programming code (which is what Excel creates when you record the macro.) Copy and paste that into this post so that people can see what your code is. Hard to diagnose without seeing the code. Dave -- Brevity is the soul of wit. "Brock" wrote: Dave, Not sure. I'm certainly no expert when it comes to marco writing. All I've done is selected "record Macro", executed a numer of steps in excel - i.e. insert row, copy down, type formula to auto number one of the cells in the inserted row etc. and clicked stop macro and assigned it to the button I have created. The first time I hit the button the marco works properly. For each additional time I hit the button the marco inserts a row above the first one that it inserted which does not work for me because each additional row added in suppose to be numbered. "Dave F" wrote: What's the code that you're using? -- Brevity is the soul of wit. "Brock" wrote: I have created a Template in Excel to be used for capital budgeting purposes. One area of the spreadsheet has a series of preformatted rows for users to input "Assumptions" supporting their analysis contained in the spreadsheet. I have added a button on the spreadsheet that when clicked is suppose to add additional preformatted "Assumptions" rows below those in the original template if necessary. I have created a macro to do said task and assigned it to the button. My problem is that the macro I created always inputs the new row above the row that I originally selected when I recorded the macro instead of following the last added row. Is there any way that I can record a macro that will only add new rows below those already created either in the original template or subsequently by running the macro. Your assistance would be greatly appreciated. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Excel 2003 FAILS, but Excel 2000 SUCCEEDS ??? | Excel Discussion (Misc queries) | |||
How to remove excel macros still there after normal delete? | Excel Discussion (Misc queries) | |||
I deleted all macros in Excel but still get dialog box | Excel Discussion (Misc queries) | |||
Are macros created in Excel 2003 compatible with Excel 5.0/95? | Excel Discussion (Misc queries) | |||
How do I update Excel 2000 macros to work in Excel 2002? | Excel Discussion (Misc queries) |