grand total column B from every worksheet in workbook

[igor]

I want to have a function in a cell that totals column B from every
worksheet in my workbook even w/o knowing how many worksheets there are or
what the name of any worksheet is.

Even better, because I would like to have a "summary" worksheet, the
function should skip column B in its own work sheet -- so, the function I'd
like might total every number in a column called "Qty" for any worksheet in
the workbook that has a column named "Qty".

I have an Excel doc that holds all of my sales data. The data is imported
as a text file from a webstore db into a worksheet named for the current
month - e.g., 05Jan. Every worksheet is based on a template, with each
column labeled. I'd like a summary sheet that would keep a running total
of sales quantity (every column labeled "Qty") -- i.e., how many units have
been sold since Day 1.

Suggestions would be appreciated. TIA. -- Igor

 

[Jason Morin]

To sum column B of every worksheet except the current
one, you could use:

Function SumColumnB() As Long
Dim ws As Worksheet
Dim TotalQty As Long
Dim SheetQty As Long
Dim ActiveWSQty As Long
Application.Volatile
ActiveWSQty = Application.Sum(ActiveSheet.[B:B])
For Each ws In ThisWorkbook.Worksheets
SheetQty = Application.Sum(ws.[B:B])
TotalQty = TotalQty + SheetQty
Next
SumColumnB = TotalQty - ActiveWSQty
End Function

--
To use, press ALT+F11, go to Insert > Module, and paste
in the code above. Press ALT+Q to close the window. Call
the function in the worksheet as:

=SumColumnB()

HTH
Jason
Atlanta, GA

 

[igor]

To sum column B of every worksheet except the current
one, you could use:

Function SumColumnB() As Long
Dim ws As Worksheet
Dim TotalQty As Long
Dim SheetQty As Long
Dim ActiveWSQty As Long
Application.Volatile
ActiveWSQty = Application.Sum(ActiveSheet.[B:B])
For Each ws In ThisWorkbook.Worksheets
SheetQty = Application.Sum(ws.[B:B])
TotalQty = TotalQty + SheetQty
Next
SumColumnB = TotalQty - ActiveWSQty
End Function

Wow, that's perfect. I did have to learn that I have to manually update
the cell to be sure that it is current as I switch between worksheets. But
after that, works fine. Thanks, Jason. -- Igor