Sum up values in each column in 12 different worksheets and displayvalue in a MsgBox

[japsmangi]

Hi,

Can anyone help me on how to sum up numerical values in each column
(say Column C) in 12 different worksheets and display value in a
MsgBox.

Because I'm quiet new to VBA I'm stuck...please help...

 

[joel]

Here are two methods. Te method to use depends on the number of sheet
in the workbook you want included and the number of sheets you want t
exclude from the total.

MyTotal = 0
for each sht in sheets
if sht.name <> "Summary" and sht.name <> "Cover Sheet" then
with sht
Lastrow = .Range("C" & rows.count).end(xlup).row
Set MyRange = .Range("1:" & LastRow)
Total = Total + worksheetfunction.sum(Myrange)
end with
end if
next sht
msgbox("the total is : " & Mytotal)



shtNames = array("sheet1","sheet3",sheet5")
MyTotal = 0
for each sht in shtnames
with sheets(sht.name)
Lastrow = .Range("C" & rows.count).end(xlup).row
Set MyRange = .Range("1:" & LastRow)
Total = Total + worksheetfunction.sum(Myrange)
end with
next sht
msgbox("the total is : " & Mytotal