Assign new pivot cache to a pivot table

[Glory]

Is this possible?

 

[Glory]

Looking here. I'm not sure how to make sure that I'm choosing the right pivot cache though. Is it possible to check the source range of a pivot cache that refers to a worksheet range?

Edit: Also, I don't actually see this method available for VBA's PivotTable object or for indexed members of the PivotTables collection. Is this only available in VB?

 

[Glory]

MsgBox ActiveSheet.PivotTables(1).PivotCache.SourceData

This successfully returns a range on the source sheet... but it's much smaller than the actual source range (which has something like ten thousand rows in it).

The exact return text reads:

'Sheet1'!C1:C10

ActiveSheet.PivotTables(1).PivotCache.SourceData = _
    "'Sheet2'!A1:J65536"

Tried this, but it gives error 1004, pivot table item not valid.

Edit: The sourcedata property is read/write.

 

[repairman615]

I don't really understand it at all. I hope this is helpful to you.

Sub ChangePivotCache()
'pivot table tutorial by contextures.com
'change pivot cache for all Pivot Tables in workbook
Dim pt As PivotTable
Dim wks As Worksheet
 
For Each wks In ActiveWorkbook.Worksheets
For Each pt In wks.PivotTables
pt.CacheIndex = Sheets("Pivot").PivotTables(1).CacheIndex
Next pt
Next wks
 
End Sub

found at
http://www.contextures.com/xlPivot11.html

 

[Glory]

I'm having to work with a lot of rows of data; more than the maximum limit for a single sheet. Instead of creating a new pivot table every time I have to create a new data sheet, it seemed like there must be a way to switch the source of the pivot tables I'm already using.

You just tracked it down. Thanks.

 

[repairman615]

That's alot of Data!