I have a workbook with the first sheet called "Totals" The other worksheets
are called "Week1", "Week2" ... "Week52" etc

I am looking for a macro that will search through all the worksheets
(ignoring "Totals") collecting information in Colums A & B and create a
summary on the totals sheet. Column A will be the size and column B will be
quantity.



Example:-



Worksheet "Week1"

"Col A" "Col B"

300x20x10 2

100x100x10 1

150x20x5 2



Worksheet "Week2"

"Col A" "Col B"

300x20x10 1

100x100x10 1

150x70x5 2





The totals sheet should look like this



300x20x10 3

100x100x10 2

150x20x5 2

150x70x5 2



Any values that are the same in Column A would have their quantities added
together. I'm maybe asking a bit to much, but if someone has done anything
like this before help would be appreciated.



Thanks in advance



John

I would be inclined to avoid the macro thing and go with a Pivot Table using
multiple consolidation ranges. On your summary sheet put the cursor in A1 and
select Data-Pivot Tables and Charts-Multiple Consolidation Ranges-Single
Page Field-Add the data ranges from all 52 sheets (that will take a few
minutes)... the pivot table should summarize your quantities by size...
--
HTH...

Jim Thomlinson


"John C" wrote:

I have a workbook with the first sheet called "Totals" The other worksheets
are called "Week1", "Week2" ... "Week52" etc

I am looking for a macro that will search through all the worksheets
(ignoring "Totals") collecting information in Colums A & B and create a
summary on the totals sheet. Column A will be the size and column B will be
quantity.



Example:-



Worksheet "Week1"

"Col A" "Col B"

300x20x10 2

100x100x10 1

150x20x5 2



Worksheet "Week2"

"Col A" "Col B"

300x20x10 1

100x100x10 1

150x70x5 2





The totals sheet should look like this



300x20x10 3

100x100x10 2

150x20x5 2

150x70x5 2



Any values that are the same in Column A would have their quantities added
together. I'm maybe asking a bit to much, but if someone has done anything
like this before help would be appreciated.



Thanks in advance



John

I am having no luck with this - the pivot table is adding up all the fields
in my column A but it doesn't calculate the quantities, should the ranges
look like this:-

Week1!$A:$B
Week2!$A:$B
Week3!$A:$B

Thanks
John




I would be inclined to avoid the macro thing and go with a Pivot Table
using
multiple consolidation ranges. On your summary sheet put the cursor in A1
and
select Data-Pivot Tables and Charts-Multiple Consolidation
Ranges-Single
Page Field-Add the data ranges from all 52 sheets (that will take a few
minutes)... the pivot table should summarize your quantities by size...
--
HTH...

Jim Thomlinson


"John C" wrote:

I have a workbook with the first sheet called "Totals" The other
worksheets
are called "Week1", "Week2" ... "Week52" etc

I am looking for a macro that will search through all the worksheets
(ignoring "Totals") collecting information in Colums A & B and create a
summary on the totals sheet. Column A will be the size and column B will
be
quantity.



Example:-



Worksheet "Week1"

"Col A" "Col B"

300x20x10 2

100x100x10 1

150x20x5 2



Worksheet "Week2"

"Col A" "Col B"

300x20x10 1

100x100x10 1

150x70x5 2





The totals sheet should look like this



300x20x10 3

100x100x10 2

150x20x5 2

150x70x5 2



Any values that are the same in Column A would have their quantities
added
together. I'm maybe asking a bit to much, but if someone has done
anything
like this before help would be appreciated.



Thanks in advance



John

Try Data-Consolidation

John C wrote:
I have a workbook with the first sheet called "Totals" The other worksheets
are called "Week1", "Week2" ... "Week52" etc

I am looking for a macro that will search through all the worksheets
(ignoring "Totals") collecting information in Colums A & B and create a
summary on the totals sheet. Column A will be the size and column B will be
quantity.



Example:-



Worksheet "Week1"

"Col A" "Col B"

300x20x10 2

100x100x10 1

150x20x5 2



Worksheet "Week2"

"Col A" "Col B"

300x20x10 1

100x100x10 1

150x70x5 2





The totals sheet should look like this



300x20x10 3

100x100x10 2

150x20x5 2

150x70x5 2



Any values that are the same in Column A would have their quantities added
together. I'm maybe asking a bit to much, but if someone has done anything
like this before help would be appreciated.



Thanks in advance



John