Thanks, Tom.

Are you aware of a way to change the SQL statement for a pivot cache that
used by more than one table?

- John

"Tom Ogilvy" wrote in message
...
PivotTables do not use other pivottables as the data source under the
scenario you describe. What they do is use the original pivotcache as the
same datasource.

Dim pCache as PivotCache
set pCache = ActiveSheet.PivotTables(1).PivotCache

will give you a reference to the pivot cache.

Regards,
Tom Ogilvy


"John Michl" wrote in message
...
I've stumbled along with some VBA programming that allows me to change
the
connection and sql properties of a pivot cache. Unfortunately, I've
learned
that it is difficult (or perhaps impossible) to change the SQL statement
for
a pivotcache that is linked to more than one pivot table. In my
workbook,
I
have a dozen or so pivottables most of which were created by either
copying
the first table or creating a new table that used the first table as the
data source.

1) Does anyone know of a work around that would allow me to change the
SQL
properties of these linked tables? (I've got the code working fine for
a
single table but not tables based on one central source.)

2) Is there away to determine which table is the initial table from
which
the others are derived? When I look at the connection properties they
all
seem to look like they connect to the SQL database not to a pivot table.

Thanks.



P.S. I've left a number of messages regarding similar topics over the
past
week with no response. Sorry for being a pest but I'm in a world of
hurt.

- John