SumFormula used for multiple cells in macro

[djmurray363]

<tbody> </tbody>

I have a series of check boxes on a sheet called "Resource Estimator." The user clicks the boxes that apply and based off the selection, two totals are generated. Often there are cases where multiple check box sheets are required for a project. The code above allows the user to generate a certain number of these check box sheets determined by the value entered entered in "A15." So if you enter 5 in "A15", 5 check box sheets are created. Then each sheet created will have totals in the corresponding "M9"s and "M10"s. A "total" sheet is then unhidden and we would like sums all of the generated "M9"s and "M10"s to be reported in cells "F6" and "F7" of the "total" sheet. The following code will take all of the "M9"s and report them to "F6" of the "total" sheet but I am stuck on how to add the same thing for "M10" sums to be reported in "F7" of the total sheet. Thanks in advance! Sub Mac() Sheets("total").Visible = True Dim i As Integer, SumFormula As String For i = 1 To Sheet1.Range("A15").Value Sheets("Resource Estimator").Copy After:=Sheets(Sheets.Count) ActiveSheet.Name = "Batch " & i If i = 1 Then SumFormula = "=SUM('" & ActiveSheet.Name & "'!M9" 'begin sum formula Else SumFormula = SumFormula & ",'" & ActiveSheet.Name & "'!M9" 'iterate sum formula End If Next i SumFormula = SumFormula & ")" 'end sum formula ThisWorkbook.Sheets("Total").Range("F6").Formula = SumFormula 'write sum formula to cell F6 End Sub

<tbody data-remaining-comments-count="0" data-canpost="true" data-cansee="false" data-comments-unavailable="false" data-addlink-disabled="false">
</tbody>

 

[Carpaccio]

Hi djmurray363

would this work?

Sub Mac()
Sheets("total").Visible = True
Dim i As Integer, SumFormula9 As String, SumFormula10 As String


For i = 1 To Sheet1.Range("A15").Value
    Sheets("Resource Estimator").Copy After:=Sheets(Sheets.Count)
    ActiveSheet.Name = "Batch " & i
    If i = 1 Then
        SumFormula9 = "=SUM('" & ActiveSheet.Name & "'!M9" 'begin sum formula
        SumFormula10 = "=SUM('" & ActiveSheet.Name & "'!M10" 'begin sum formula
    Else
        SumFormula9 = SumFormula9 & ",'" & ActiveSheet.Name & "'!M9" 'iterate sum formula
        SumFormula10 = SumFormula10 & ",'" & ActiveSheet.Name & "'!M10" 'iterate sum formula
    End If
Next i


SumFormula9 = SumFormula9 & ")" 'end sum formula
SumFormula10 = SumFormula10 & ")" 'end sum formula
ThisWorkbook.Sheets("Total").Range("F6").Formula = SumFormula9 'write sum formula to cell F6
ThisWorkbook.Sheets("Total").Range("F7").Formula = SumFormula10 'write sum formula to cell F7


End Sub