Excel 2007 :: Pivot Table Cache Size Limit With VBA

I am trying to create multiple pivot tables from the same pivotcache using VBA. The data range is approximately 270,000 records with 100 columns. When I run the macro, I get "Run-time error '-2147352567 (80020009)' Method 'Add' of object 'PivotCaches' failed". Is there a size limit on the data? If there is less than 65,000 records, it works great; if I use a wizard, it works with no problems with the full dataset. If I bring the data in as an Access table, it can work with some tweaking of the code, but the size of the file is huge because each pivot is taking its own snapshot of the data. I also can only create 3 tables before it runs out of memory, so I have to stop, save the file, and open it back up to create 3 more pivots.

Dim WSD As Worksheet
Dim PTCache As PivotCache
Dim PT As PivotTable
Dim PRange As Range
Dim FinalRow As Long

[Code] .........

The last line is where I am getting the error. I am running XP, Excel 2007.

As I said, if I import an Access table, I can use the code:

ActiveWorkbook.PivotCaches.Create(SourceType:=xlDatabase, SourceData:= _
"Table_Pivot_test.accdb[#All]", Version:=xlPivotTableVersion12). _
CreatePivotTable TableDestination:="", TableName:=txtPivotTableName, DefaultVersion:=xlPivotTableVersion12

And loop, but the final file size with 14 pivots can be 500MB! I have tried both "Create" and "Add", but they both give me errors.