Create 1 pivot table from multiple worksheets

**rim78** · 10-02-2007, 02:13 AM

Hello all,

I am trying to create a pivot table that contains data from more than 1 worksheet. I've tried 'multiple consolidation ranges' options but it only gives 4 field list as headings (Row,Column,Value,Page1).

All my worksheets have same column headings in first row. I had to split the data into worksheets because of the max 65536 rows in Excel.

How can I create the pivot table with the first row as the field list?

Thanks in advance for your help.

**VBA Noob** · 10-02-2007, 05:01 AM

Take a look at the example on this site

Multiple Consolidation Ranges

VBA Noob

**rim78** · 10-03-2007, 05:34 AM

Hi VBA Noob,

Thanks for replying.
I have decided that doing the pivot table based on multiple worksheets is not the best solution in my case, because then I have to add and update every month in that file. Which means more maintenance. The file I'm working with is appended every month with data.

I'm now experimenting with OLAP cube and ODBC query files, finding out which one is the fastest and best implemented in my case. So far I haven't seen any difference in both method.