Instead of adding a pivotcache in the code, create a pivot table from an
existing pivotcache, e.g.:
Dim pc As PivotCache
Dim ws As Worksheet
Dim pt As PivotTable
Set ws = Worksheets.Add
Set pc = Worksheets("Pivot").PivotTables(1).PivotCache
Set pt = pc.CreatePivotTable(TableDestination:=ws.Range("B3 "), _
TableName:="SalesPivot")
With pt
.AddFields RowFields:="Region"
.PivotFields("Units").Orientation = xlDataField
End With
Mike D. wrote:
Hi. I currently have a program that creates a pivot table. I want to create
another pivot table that keys off of the same data, but would like to use the
"cached" data rather than re-use the same data. I have the second pivot
table command below. How would I rewrite this to reference the cached data?
Thanks, Mike.
ActiveWorkbook.PivotCaches.Add(SourceType:=xlDatab ase, SourceData:= _
"RAWData!A1:AE" & lastRow).CreatePivotTable TableDestination:="",
TableName:= _
"PivotTable4", DefaultVersion:=xlPivotTableVersion10
--
Debra Dalgleish
Contextures
http://www.contextures.com/tiptech.html