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
-----Original Message-----
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
.
|