Is is possible to tag or name a PivotCache?

Glory

Well-known Member
Joined
Mar 16, 2011
Messages
640
I’m swapping the source range of a pivot table using this method:
<?xml:namespace prefix = o ns = "urn:schemas-microsoft-com:office:office" /><o:p> </o:p>
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.
<o:p> </o:p>
The problem is that when I do this more than once, the new cache begins to bump the older cache up, taking its position for the duration of the code’s execution.
<o:p> </o:p>
CacheIndex 5 becomes 6, and the new cache takes position 5.
<o:p> </o:p>
So I’m looking for a way to name caches the way you can name some other objects. Is there any way to do this?
<o:p> </o:p>
Or is there any simpler way to change the source range of an existing pivot table?
 

Excel Facts

Whats the difference between CONCAT and CONCATENATE?
The newer CONCAT function can reference a range of cells. =CONCATENATE(A1,A2,A3,A4,A5) becomes =CONCAT(A1:A5)
Code:
ExcessPages = 1
 
ThisWorkbook.PivotCaches.Add _
    (SourceType:=xlDatabase, _
    SourceData:=Sheets("Sum Error").Range("A:J")) _
    .CreatePivotTable _
    TableDestination:=ThisWorkbook.Sheets("Sum Error Pivot" &
 ExcessPages).Range("D1"), _
    TableName:="Table2"
 
[B][I]Table2Cache[/I][/B] = ThisWorkbook.Sheets("Sum Error Pivot" & _     
    ExcessPages).PivotTables("Table2").CacheIndex
 
ThisWorkbook.Sheets("Sum Error Pivot" & _
    ExcessPages).PivotTables(2).CacheIndex = [B][I]Table2Cache[/I][/B]

ThisWorkbook.Sheets("Sum Error Pivot" & _
    ExcessPages).Range"D1:E2").Clear

I can refer to each of the placeholder tables by name. So I'm just setting a variable with the placeholder table's cacheindex, and using that.
 
Upvote 0

Forum statistics

Threads
1,224,603
Messages
6,179,854
Members
452,948
Latest member
UsmanAli786

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top