Pivot cache when pivot table copied

[Michael Glenn]

Hello,

I have a worksheet that contains a pivot table which uses an external data
source (SQL). I've written a script to update the SQL statement in the pivot
cache so the user can simply change some query criteria on the worksheet and
update the pivot table (specifically, the user can enter a name in a cell and
the pivot table displays work data pulled from MS Project server). I would
like the user to be able to copy the worksheet, change the name and update
the pivot table (ex.: so he can create one worksheet per team member). When
I make a copuy of the worksheet the script doesn't work. I've found that the
pivot table initialy has the same pivot cache as the original (same cache
index). The only way I have found to get a new pivot cache is to manualy go
and change the data source for the pivot table...this creates a new pivot
cache and the script works fine after that. Is there anyway to do this
programatically?

Thanks

 

[Michael Glenn]

Ok...found this workaround:

Dim ptProjects As PivotTable
Dim NewPC As PivotCache
Dim NewPT As PivotTable

'*** Get a refrence to the current pivot table on the new worksheet
Set ptProjects = ActiveSheet.PivotTables("ResProjects")

'*** Create temporary pivot table with new pivot cache in out of the way
location
Set NewPC = ActiveWorkbook.PivotCaches.Add(xlExternal)
NewPC.Connection = "ODBC connection string goes here"
NewPC.CommandText = "SQL statements"
NewPC.CommandType = xlCmdSql
Set NewPT = NewPC.CreatePivotTable(Range("Z1"), "Temp")
NewPC.Refresh

'*** Link current pivot table to new pivot cache associated to temp pivot
table
ptProjects.CacheIndex = NewPT.CacheIndex

'*** Delete temporary pivot table
Columns("Z:AD").Select
Selection.Delete Shift:=xlToLeft
Range("A1").Select