Macro to compile totals of values on multiple worksheets
 

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

Macro to compile totals of values on multiple worksheets
 

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

Macro to compile totals of values on multiple worksheets
 

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

Macro to compile totals of values on multiple worksheets
 

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