Tom,

Try this, see if it works as you want for the formula insertion part
of it. As for the SUM not updating... depends on what version of
Excel you're running. What I tend to do to ensure that SUMs
automatically update is include a "short row" before the SUM function.
What I mean by this is: Say you have figures in cells A1 to A5, and
a sum function in A6 (=SUM(A1:A5)). Depending on the Excel version
you're using, inserting a row in row 6 to hold a new item will have
one of two effects: It will either update the sum formula to
"=SUM(A1:A6)" (for newer versions), or leave it as "=SUM(A1:A5)" (for
older versions, which produces an incorrect result).

So I use a short row. A1:A5 hold values. A7 has the sum formula,
"=SUM(A1:A6)". In row 6, I put a line of _ characters (the number of
which depends on the size of the numbers you're dealing with), and set
the row height to 4. This serves two purposes - using _ instead of
border settings means you get a gap in the line between cells, which
(IMHO) looks better, and (more importantly) if you want to add
something to the sum range, you put the cursor in row 6 and insert the
row - the new row gets inserted within the existing sum range, and
therefore gets included into the sum formula regardless of the version
of Excel the end user is on.

Hope this helps,

Clayton.

========================================


Sub InsertRowWithExistingFormula()
Selection.EntireRow.Insert
Selection.EntireRow.FormulaR1C1 = _
Selection.EntireRow.Offset(-1, 0).FormulaR1C1
End Sub