copying formulas from above or below row into inserted row

Hello,

When I insert a row in my table, I want the formulas from the above row (or below row) to be copied down to my newly inserted row. I say "below row" as well in case I want to insert a new first row and want the formulas in the row underneath to be copied up.

e.g., formulas currently in the first row are:

Column A: =SUBTOTAL(3, $B$5:B5)
Column H: =IF((F5="N*"),(1),"")
Column I: =IF(((SUM(H$4:$H5))=(SUM(H3:$H$4))),"",(SUM(H$4:$H5)))
Column J: =I5

Any other values in the other columns should not be copied to the new row.

I also do not want my table to be scrambled if I should delete any particular row.

Thank you.

I'm assuming that all of the rows within your target area have the same formula(s). If that's the case, then you can create a row in the spreadsheet that contains only the formulas you want to copied, and name that row "Form_Row".

Then create a control button that calls the following code:

Sub InsertRow()
    ActiveCell.Offset(0, -ActiveCell.Column + 1).Select
    Selection.EntireRow.Insert
    Range("Form_Row").Copy
    Selection.PasteSpecial
    Application.CutCopyMode = False
End Sub

Now, place the cursor where you want the new row to be (any column) and hit the button.

Hope this helps

SAE