How to source data from multiple worksheets in a PivotTable?

[Wally]

I am trying to use 3 worksheets in my "Range" during step 2 of 3 for the
PivotTable Wizard.

I HAVE tried to use the multiple consolidation ranges option but I keep
getting junk in my PivotTable along with error messagess that the data is
truncated.

Each of the three worksheets has 199, 102, and 159 columns of field names,
respectively, which is why I can't combine all the data into one worksheet
(or one table in Access, which is where the data originates). This also
prevents me from creating a named range for a 3-D reference (since the number
of columns are not the same) as the source. Even if each worksheet ahd the
same number of columns, I still doubt that the PivotTable Range would take a
3-D reference named range.

I would love to do this programmatically rather than with the PivotTable
wizard but I still don't see how it is possible.

If there are any PivotTable gurus out there, here's your chance to shine!

 

[Jimbola]

Hi Wally,

Unfortunately to my knowledge you can not combine data from seperate origins
into one table even if they have the exact same headings.
2 solutions are

1) Create several pivot tables far away somewhere, one on top of each other,
and then create a single pivot table based on these.

2) Using the MS Query facility in Excel create a query getting all you
source data into one worksheet and then basing your pivot table on that.

HTH

J

 

[Matt Jensen]

See if this helps
http://edferrero.m6.net/Pivot20032.html#4
Matt