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>