VBA - update formula when creating a new sheet

[ellisa21]

Hello,

I have several sheet here

- Total is the calculation of all sheets (exclude Template)
- Template is the master sheet of Nancy, Quincy, Bella, etc

Step on macro :
1. Add new sheet
2. Copy and paste 'Template' sheet
3. Calculate the total on 'Total' sheet (sum formula)

The problem is when I run the second time, the total calculation of last new sheet doesn't include in the calculation.
eg:
1. Run the macro and create a new sheet 'testcopy'
3. Run the macro again
4. Macro create a new sheet 'copy' but the formula doesn't include 'testcopy'

My question : how can I update the formula?

'   Start calculation
    Range("L6").Select
    Application.CutCopyMode = False
    ActiveCell.FormulaR1C1 = "=Quincy!RC+Nancy!RC+Bella!RC+Abel!RC+Copy!RC"
    Range("L6").Select
    Selection.AutoFill Destination:=Range("L6:L21"), Type:=xlFillDefault
    Range("L6:L21").Select
    Selection.AutoFill Destination:=Range("C6:L21"), Type:=xlFillDefault
    Range("C6:L21").Select
    ActiveWindow.SmallScroll Down:=3