Having Code Store SUM Function into Particular Cell on sheet Copy

[USFengBULLS]

Hello,

I have a procedure that makes a copy of a sheet that on the new sheet I do not want it to copy the embeded formulas, just the values, which works fine.
But, I do need it to have one cell in particular, Range("D7"), to have this formula in it =SUM(D4:D6) in case the user has the need to come back to this particular Change Order and adjust the cost for this Change Order it reflects in the Total Cell Range ("D7"). Here is the code so far:


Sheets("NEW FORM-RESET").Select
Sheets("NEW FORM-RESET").Copy After:=Sheets(3)
Sheets("NEW FORM-RESET (2)").Select
Sheets("NEW FORM-RESET (2)").Name = CO_Number
ActiveSheet.UsedRange.Value = ActiveSheet.UsedRange.Value
Range("D7") = Application.WorksheetFunction.SUM(Range("D4:D6"))

Notice I use the Application.WorksheetFunction.Sum for this but it of course only runs this sum once which does not satisfy say the user comes back in a week from to this sheet and need to make an adjustment to costs. How can I write it where it is like the excel formula =SUM(D4:D6) or even =(D4+D5+D6)?

 

[mikerickson]

Range("D7").Formula = "=SUM(D4:D6)"

 

[USFengBULLS]

Thank you mikerickson