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?
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?
VBA Code:
' 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