Excel Macros

[Guest]

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.

 

[Guest]

What's the code that you're using?

 

[Guest]

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.

 

[Gord Dibben]

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

 

[Guest]

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

 

[Guest]

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.

 

[Guest]

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