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

[patel]

Set Myrange = Range("C1:C" & LastRow)

[tyantorno]

Hello patel,

Unfortunately it looks like it is still summing each worksheet. Example:
I put (1) in col c sheet1, I put (1) and (1) in col c sheet 2, (1) put (1) and (1) and 1 in col c sheet3. The results I get is msgbox sheet1 = (1), msgbox sheet2 = (3) - instead of (2), and finally msgbox sheet 3 = (6), a total of all the sheets. Any other suggestions would be very helpful. Thank you.

[austenr]

Are you wanting a message box at the conclusion of the sub saying the total for col C Sheet 1 is blah,

the total for column C worksheet2 is blah, etc???

If so you have a couple of choices. Store each total in an array element then spit the array out at the end of the sub in a message box with a carriage return after each line, or if you want to do the crude method, assign a varaible to each ws then load it with the total ferom col C and zero it out before you proceed to the next worksheet.

Personally, the array is cleaner and is more flexible if your sheet count is an unknown.

Also this is cross posted here:

http://www.mrexcel.com/forum/excel-questions/682520-visual-basic-applications-code-sums-column-c-all-active-worksheets.html

with a solution.