Macro to compile totals of values on multiple worksheets

[John C]

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

 

[Jim Thomlinson]

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

 

[John C]

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

 

[equiangular]

Try Data->Consolidation