Adding rows using macro

[NANNER]

This is my first macro! (Excel 97 running under Windows
2000.) I have a form that I want to add rows (one row at a
time) after the current row. The form allows businesses to
send in changes to their products. They may have 1 or 100
products that need to be updated. We want the form to
initially have one row for them to enter their
information. If they have another product, then we want a
row to be added automatically after the current row. It
looks like this:

A B C D E
1 Product Name Size Quantity Curr. Price New Price
2 Popso 1L 5 cases $20.00 $25.00

Since Excel does not allow cells to be split, the form is
made up of different merged cells. I placed the insertion
point after the current cell and started the Record Macro,
clicked on Insert Row, then used Format Painter to format
the row the same as the row above. End Macro.

I want to add a row after Row 2, which would be Row 3 and
should be formatted the same as Row 2. If add'l products,
then add Row 4, etc. The first time the macro is run, it
works ok, but after the first time, the row is inserted at
the point where I ran the macros, into Row 3. How can I
get the rows added after the current row each time? I've
tried doing several different ways, but can't get it to
work the way I want. This is the macro:

Sub add_lines()
'
' add_lines Macro
' Add lines one at a time
'
' Keyboard Shortcut: Ctrl+l
'
Selection.EntireRow.Insert
Range("A9K9").Select
Selection.Copy
Range("A10K10").Select
Selection.PasteSpecial Paste:=xlFormats,
Operation:=xlNone, SkipBlanks:= _
False, Transpose:=False
Application.CutCopyMode = False
End Sub

One of the programmers tried it and had the same results.
He also added a Control Box to run the macro off to the
side, which when clicked will add the row. This is a nice
touch, but only if it will move with each row addition.
The macro looks like this:

Sub New_Record()
'
' New_Record Macro
' Macro recorded 5/4/2004
'
' Keyboard Shortcut: Ctrl+z
'
Rows("10:10").Select
Selection.Insert Shift:=xlDown
Range("A9K9").Select
Selection.Copy
Range("A10").Select
ActiveSheet.Paste
Application.CutCopyMode = False
Selection.ClearContents
End Sub

How can I add rows when needed, AFTER the current row, and
not Before or at the point I recorded the macro? Either
simply by hitting the ENTER key or some other way? Need to
be formatted correctly though. Having a Control Button
move with each row would be nice too. Can anyone help? And
remember, this is my first attempt. Thanks! (or, Should I
have posted this on a different forum?)

 

[jeff]

Hi,

Perhaps work this into your code when defining your
current range after each insert:

Dim newrange As Range

'here is the last row of data
lastrow = ActiveSheet.Cells(Rows.Count, "A").End(xlUp).Row

'newrange is now expanded for latest data
Set newrange = Range("A1:A" & lastrow)


This will give you the updated range rather than
just a pre-defined one.

jeff