Found some interesting code:

[Djblois]

I found this code browsing through the help files:

[VBA]Worksheets(1).PivotTables("Pivot1") _
.PivotCache.OptimizeCache = True
[/VBA]

Unfortunately, I can't get it to work. I tried looking all through the help to figure out how to use it but it just says use it to optomize PivotCache when its constructed. I have 2 questions:

1) Theoratically should this speed up the creation of pivot tables?

2) Why won't it work?

I tried it before the pivotcache was created and after it was created. they both give me the Application defined or object defined error.

Here is my full code:

[VBA]Set pRange = workingBook.Cells(1, 1).Resize(finalRowDetail, finalColumnDetail)
PT.PivotCache.OptimizeCache = True
Set ptCache = WB(1).PivotCaches.Add(SourceType:=xlDatabase, SourceData:=pRange.Address)
Set PT = ptCache.CreatePivotTable(TableDestination:=pvt.Range("A1"), TableName:="PivotTable")[/VBA]

[Norie]

Why are you setting a reference to the pivot table after this?
[vba]
PT.PivotCache.OptimizeCache = True

[/vba]

[Djblois]

thanks. my mistake but I also tried it after the reference. Do you know if that should speed up a pivot table?

[Andy Pope]

The remark in the help file is not too promising.

Remarks
Cache optimization results in additional queries and degrades initial performance of the PivotTable report.

[Djblois]

lol Thanks Andie. I know that is why I am confused. I am starting to think it only works with external data but if anyone knows differently please tell me.