Consistently add new row above

[Peterino]

Hi. I would like to have a simple macro that automatically adds a new row above the entry row. Unless there is a better way to have the most current entry on top without having to constantly sort.

Also I would like to have formulas added to new row. See attached example.

Thank you.

Peter

[Richard Buttrey]

Difficult to comment without seeing your workbook and what you consider to be an entry row.

That said whenever I need to use a database and add new records in a consistent manner the approach I generally use is to create a data entry row above the main table / database range. Then attach a few lines of code to a button that first inserts a new top row to the database, then copies the data entry row to the new blank row and finally clears the entry row ready for the next record.

So for instance with the database column labels in say row 5, and a data entry range say A2:H2 and named "NewRecord" and the database range named with the dynamic range name "Data" defined as

=OFFSET($A$5,0,0,COUNTA($A:$A),8)

Sub AddRecord()
    Range("Data").Cells(2, 1).EntireRow.Insert
    Range("NewRecord").Copy
    Range("Data").Cells(2, 1).PasteSpecial (xlPasteValues)
    Range("NewRecord").ClearContents

End Sub

[Peterino]

Hi Richard. Just checking to see if you saw my earlier reply. Peter

[Peterino]

Hello Richard. I guess I am pretty novice with code stuff. I figured out how to do data entry range and database names, but I couldn't figure out where to apply the formula. I also tried adding the vbs which worked when I clicked View, Macro, Run, but not when I assigned it to a button. Also, I did have a sheet attached to my post. Peter

[nigelog]

You could add to a button at the top of your sheet

Sub addrow()
Rows(3).Insert Shift:=xlDown
Cells(3, 9).Formula = "=IF(G3="""","""",F3*G3+H3)"
Cells(3, 12).Formula = "=IF(J3="""","""",F3*J3-K3)"
End Sub

[Peterino]

Hi Nigelog

That works. Much appreciated!

Peter