SumFormula used for multiple cells in macro

djmurray363

New Member
Joined
Mar 20, 2017
Messages
3

<tbody>
</tbody>

<tbody data-remaining-comments-count="0" data-canpost="true" data-cansee="false" data-comments-unavailable="false" data-addlink-disabled="false">
[TD="class: comment-text"]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

[/TD]

</tbody>
 

Excel Facts

VLOOKUP to Left?
Use =VLOOKUP(A2,CHOOSE({1,2},$Z$1:$Z$99,$Y$1:$Y$99),2,False) to lookup Y values to left of Z values.
Hi djmurray363

would this work?

Code:
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
 
Upvote 0

Forum statistics

Threads
1,223,893
Messages
6,175,239
Members
452,621
Latest member
Laura_PinksBTHFT

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top