Updating total formula after inserting a row

Hey all,

I need your help with a macro that has been driving me crazy. It is probably easiest if you look at the attached spreadsheet, but I'l describe it here too.

I have a macro that inserts a row where I need it based on my selection in a dropdown box. The row below the newly inserted row is a total row that sums of the rows above (usually 2-8 rows summed). However after I add the new row the total row does not incude the new row into the formula. Thus if I enter values into the new row they are not summed to the total, but I need them to be included in the sum.

Any help would be really appreciated.

Thank you!!

That is not working. My macro is shifting the rows down. Can you help me then adjust my current macro.

Sub InsertRow()
Dim Rng As Range
Set Rng = Range("C7:C200").Find(Range("C4").Value, [C7], xlValues, xlWhole, xlByColumns, xlNext, False)
Rng.EntireRow.Insert Shift:=xlDown
Rng.Offset(-2, 0).EntireRow.Copy
Rng.Offset(-1, 0).EntireRow.PasteSpecial xlPasteFormats