I have a massive file (over 200mb) with over a dozen seperate sheets
all containing at least 3 pivot tables each. They all come from the
same data source (a sheet with an external data query) and have
multiple page, column and row fields.

I have been looking at removing the pivot tables for some time as all
the pivot tables end up being added together on a summary sheet with
relative and absolute formulas referring to cell ranges within the
pivot tables that have to be changed on each refresh to line up the
correctly.

However some of he fields have hidden criteria (double click on field
and select items to hide) and some visible criteria (selected by drop
down arrow). Also some fields have been dropped in twice (creating
"[Field]" and "[Field]2", further adding to my confusion).

I have found a lot of articles on contructing a pivot table from
scratch, however I cannot find anything on deconstructing (I found one
article for 2007, however I am using 2003 SP2) a non-OLAP pivot
table.

Does anyone know of a way of doing this as I have spent the last
couple of hours scouring the object model for all things "Pivot....."
and come up blank.

Thank you for your time.
Giff

Describe how you want to "deconstruct" your pivot tables.

- Jon
-------
Jon Peltier, Microsoft Excel MVP
Tutorials and Custom Solutions
Peltier Technical Services, Inc. - http://PeltierTech.com
_______


"Giff" wrote in message
...
I have a massive file (over 200mb) with over a dozen seperate sheets
all containing at least 3 pivot tables each. They all come from the
same data source (a sheet with an external data query) and have
multiple page, column and row fields.

I have been looking at removing the pivot tables for some time as all
the pivot tables end up being added together on a summary sheet with
relative and absolute formulas referring to cell ranges within the
pivot tables that have to be changed on each refresh to line up the
correctly.

However some of he fields have hidden criteria (double click on field
and select items to hide) and some visible criteria (selected by drop
down arrow). Also some fields have been dropped in twice (creating
"[Field]" and "[Field]2", further adding to my confusion).

I have found a lot of articles on contructing a pivot table from
scratch, however I cannot find anything on deconstructing (I found one
article for 2007, however I am using 2003 SP2) a non-OLAP pivot
table.

Does anyone know of a way of doing this as I have spent the last
couple of hours scouring the object model for all things "Pivot....."
and come up blank.

Thank you for your time.
Giff

I'm looking to get the complete criteria for summarized fields in the
DATA area of the pivot table.

If the pivot tables were straight from the external datasource I would
happily extract the SQL statement, or if they were formula based I
could easily 'back-track' them. However I can't find an easy was of
getting the criteria for range based pivot tables.

Sorry if this seems a bit vague, I am relatively new to the Pivottable
(and related objects) model.

Giff