Pivotcache object doesn't survive creation

[Glory]

Unless it's attached to a pivot table during its creation, I can't get a new pivotcache object to survive at all. What i'm reading seems to indicate that a pivot cache should survive at least until I've saved the workbook.

This is important because I've got more than 65000 rows of data that I need to pivot, and I'd prefer not to have to create a new table for every new sheet of data if I can possibly avoid it (adds ten seconds or more to the macro's runtime).

ThisWorkbook.PivotCaches.Add _
    (SourceType:=xlDatabase, _
    SourceData:=Sheets("Sheet1").Range("A:J")
 
MsgBox ThisWorkbook.PivotCaches.Count

The messagebox returns zero, unless I use the createpivottable method in the same line as the pivotcaches.add statement. But like I said, I don't want to have to create a new pivot table.

I've been experimenting with the other properties and methods of the pivotcaches.add method, but so far, no luck getting the pivotcache to survive long enough to use the CacheIndexes method to assign my existing pivot table a new cache.

So what I've been trying to instead is change the SourceData property of the pivot table. VBA's help says that's a read/write property, but I can't figure out how to write to it.

Can anyone help me out with this?

 

[Norie]

Why don't you create a reference to the pivot cache you are creating?

 

[Glory]

I don't understand what you mean. Include another statement in the same line as the "PivotCache.Add" method?

What statement?

 

[Glory]

I'm going to try using the PivotTableWizard method to edit the pivot table I've already got.

Edit: It doesn't work very well though. The code that appears when I record a macro refers to the source range "C1:C10", even though I used "A:J" (all rows in these columns). All the row, column and page fields I established disappear too, even though the new reference uses the same headers.

But this method is what appears when I right-click, then use the wizard to manually edit the source. I dunno what else to do.

 

[Glory]

Excel '03 doesn't even support the ChangePivotCache method. I've resorted to:

1) Creating a new pivot table on the same line the new cache is created;
2) Inserting no fields;
3) Reassigning the new cache as the cache of the old table; then,
4) Removing the new table.

This leaves only the old table with the new pivot cache... in under ten seconds, as opposed to the thirty which it took to create an entirely new pivot table (and assign all the properties and setup qualities).