How to sum a column of each sheet

[ExcelPupper]

I have here a set of data wherein I need to make separate sheets for each class and add sum function on the row after the last data.
My problem is how to insert sum function for each sheets. Is it possible to use For Each Next Loop here?
Kindly check my data below:

Sub SumClass()
'
'
'
' Keyboard Shortcut: Ctrl+Shift+A
'
    Rows("1:1").Select
    Selection.AutoFilter
    Range("A1").Select
    ActiveSheet.Range("$A$1:$C$6").AutoFilter Field:=1, Criteria1:="=AB", _
        Operator:=xlOr, Criteria2:="=AC"
    Range("A1:C6").Select
    Selection.Copy
    Sheets.Add After:=ActiveSheet
    ActiveSheet.Paste
    Sheets("Sheet2").Select
    Sheets("Sheet2").Name = "A Class"
    Range("C4").Select
    Application.CutCopyMode = False
    Application.CutCopyMode = False
    ActiveCell.FormulaR1C1 = "=SUM(R[-2]C:R[-1]C)"
    Range("C5").Select
    ActiveSheet.Previous.Select
    Selection.End(xlUp).Select
    ActiveSheet.Range("$A$1:$C$6").AutoFilter Field:=1, Criteria1:="=b*", _
        Operator:=xlAnd
    ActiveSheet.Range("$A$1:$C$6").AutoFilter Field:=2, Criteria1:="=1*", _
        Operator:=xlAnd
    Range("A1:C6").Select
    Selection.Copy
    Sheets.Add After:=ActiveSheet
    Sheets("Sheet3").Select
    Sheets("Sheet3").Name = "B Class"
    ActiveSheet.Paste
    Range("C4").Select
    Application.CutCopyMode = False
    Application.CutCopyMode = False
    ActiveCell.FormulaR1C1 = "=SUM(R[-2]C:R[-1]C)"
    Range("C5").Select
End Sub

 

[lrobbo314]

How about this?

Sub SUMS()
Dim AR(0 To 1) As String: AR(0) = "A Class": AR(1) = "B Class"
Dim LR As Long, ws As Worksheet

For i = LBound(AR) To UBound(AR)
    Set ws = Sheets(AR(i))
    LR = ws.Range("C" & Rows.Count).End(xlUp).Row + 1
    ws.Cells(LR, 3).FormulaR1C1 = "=SUM(R[-" & LR - 2 & "]C:R[-1]C)"
Next i

End Sub

 

[ExcelPupper]

How about this?

Sub SUMS()
Dim AR(0 To 1) As String: AR(0) = "A Class": AR(1) = "B Class"
Dim LR As Long, ws As Worksheet

For i = LBound(AR) To UBound(AR)
    Set ws = Sheets(AR(i))
    LR = ws.Range("C" & Rows.Count).End(xlUp).Row + 1
    ws.Cells(LR, 3).FormulaR1C1 = "=SUM(R[-" & LR - 2 & "]C:R[-1]C)"
Next i

End Sub

Thank for this, may I just ask if when using For Each Next Loop it is a requirement to define the sequence of sheets?

 

[lrobbo314]

No it is not a requirement. You can loop through each worksheet in the workbook and just add a condition in the if statement to test the name of the sheet.

Sub sumsII()
Dim LR As Long, ws As Worksheet

For Each ws In ActiveWorkbook.Worksheets
    If ws.Name <> "Sheet You Don't Want a Total On" Then 'Change this line to ignore sheets
        LR = ws.Range("C" & Rows.Count).End(xlUp).Row + 1
        ws.Cells(LR, 3).FormulaR1C1 = "=SUM(R[-" & LR - 2 & "]C:R[-1]C)"
    End If
Next ws

End Sub

 

[ExcelPupper]

No it is not a requirement. You can loop through each worksheet in the workbook and just add a condition in the if statement to test the name of the sheet.

Sub sumsII()
Dim LR As Long, ws As Worksheet

For Each ws In ActiveWorkbook.Worksheets
    If ws.Name <> "Sheet You Don't Want a Total On" Then 'Change this line to ignore sheets
        LR = ws.Range("C" & Rows.Count).End(xlUp).Row + 1
        ws.Cells(LR, 3).FormulaR1C1 = "=SUM(R[-" & LR - 2 & "]C:R[-1]C)"
    End If
Next ws

End Sub

May I just ask how to understand this vba formula ' ws.Cells(LR, 3).FormulaR1C1 = "=SUM(R[-" & LR - 2 & "]C:R[-1]C)" '
Thanks!