VBA code that sums column "C" in all active worksheets

[tyantorno]

Hello,

Basically what is happening with my code is I create many worksheets which have a dollar amounts
in column C of each worksheet. The code sums the column C in the first worksheet - e.g. sum of column C is 15 for worksheet 1 - and it will display a message box with total 15, it then sums the column C of the second worksheet - e.g. sum of column C is 10 for worksheet 2 - it then displays a message bow which is worksheet 1 (15) + worksheet 2 (10) and displays a second msgbox of (25), I would like the msgboxs to display the sum of column C of each worksheets separately, not summing them together. Any questions please feel free. I greatly appreciate any assistance.

Here is the code:

Sub Step1()

Dim ws As Worksheet
Dim LastRow As Integer
Dim Myrange As Range
Dim Total As Double

For Each ws In ThisWorkbook.Worksheets
Sheets(ws.Name).Select
LastRow = Range("C" & Rows.Count).End(xlUp).Row

Set Myrange = Range("1:" & LastRow)
Total = Total + WorksheetFunction.Sum(Myrange)

MsgBox "Total For " & ws.Name & " " & Total & "" 'This works but still adds all sheets need single ofr each sheet

Next ws

End Sub

 

[BAlGaInTl]

Try this instead. You can clean up as you see fit.

Sub Step1()

Dim ws As Worksheet
Dim LastRow As Integer
Dim Myrange As Range
Dim Total, wsTotal As Double

For Each ws In ThisWorkbook.Worksheets
Sheets(ws.Name).Select
LastRow = Range("C" & Rows.Count).End(xlUp).Row

Set Myrange = Range("1:" & LastRow)
wsTotal = WorksheetFunction.Sum(Myrange)
Total = Total + wsTotal

MsgBox "Total For " & ws.Name & " " & wsTotal & ". Total for Workbook " & Total & "."

Next ws

End Sub

 

[Tom Urtis]

Off the top of my head, after this line:
Sheets(ws.Name).Select

You could enter this statement:
Total = 0

That starts your summing at zero inside the loop for each sheet. Then, change the wording of your message box prompt accordingly. You don't need to actually select the sheets to do this operation, but that's a matter you can certainly ask about if and when you are ready. In the meantime, see if this suggestion does what you want, if I understood it correctly.

Finally, this is line
Dim Total, wsTotal As Double

Should be changed to this:
Dim Total as Double, wsTotal As Double


Otherwise, you have declared Total as a Variant, which is the data type assumed by Excel VBA when a data type for each variable, even on the same line as you have it, is not specified.

 

[tyantorno]

Tom and BAlGaInTl,

Thank you so much, the code works like a charm. Also, thank you for explanation. Have a great night and thanks for your generosity. Tom

Off the top of my head, after this line:
Sheets(ws.Name).Select

You could enter this statement:
Total = 0

That starts your summing at zero inside the loop for each sheet. Then, change the wording of your message box prompt accordingly. You don't need to actually select the sheets to do this operation, but that's a matter you can certainly ask about if and when you are ready. In the meantime, see if this suggestion does what you want, if I understood it correctly.

Finally, this is line
Dim Total, wsTotal As Double

Should be changed to this:
Dim Total as Double, wsTotal As Double


Otherwise, you have declared Total as a Variant, which is the data type assumed by Excel VBA when a data type for each variable, even on the same line as you have it, is not specified.