Replacing Pivot table cache

[dustdaniel]

Hi Everyone,

A couple of years ago I found in a different forum, that you can change the cache ID from a pivot table, you just need 2 functions to do so and I need your help to find what these are.

The purpose is to change the source cache from one pivot table and replace it with the cache from another pivot table in the same excel file.

I remember you had to click on one pivot table, then open visual basic with ALT+F11 then CTRL+G and write someting like

?ActiveWorkbook.XXXXX

This would return the Cache # (1, 2, 3, 4, whatever) of the pivot table.

Next step would be to click on the pivot table I want to chance source and go back to Visual Basic and write the second fuction that I don't recall

ActiveWorkbook.XXXXX but at the end yo include the cache # from the previous consult since this would override the cache #

I hope this enough information, It's clear that I have very basic VBA knowledge, please help me getting these two pieces I'll make sure I won't loose them again!

 

[Alex Blakenburg]

You will need to have the right sheet active and put in your pivot table name
? ActiveSheet.PivotTables("PivotTable6").CacheIndex

Then without the "?" the below:
using the index no you got in the line before and using the right sheet and pivottable name
ActiveSheet.PivotTables("PivotTable1").CacheIndex = 1

Additional resource:

Excel Macros for Pivot Table Pivot Cache - Lists, Info

View pivot tables pivot cache information by using macros in this pivot table tutorial; see cacheindex, memory used, record count.

www.contextures.com

 

[RoryA]

Welcome to the MrExcel Message Board!

Cross-posting (posting the same question in more than one forum) is not against our rules, but the method of doing so is covered by #13 of the Forum Rules.

Be sure to follow & read the link at the end of the rule too!

Cross posted at:

Change pivot table cache | Microsoft Community Hub

Hi Everyone, A couple of years ago I found in a different forum, that you can change the cache ID from a pivot table, you just need 2 functions to do so...

techcommunity.microsoft.com

There is no need to repeat the link(s) provided above but if you have posted the question at other places, please provide links to those as well.

If you do cross-post in the future and also provide links, then there shouldn’t be a problem.

 

[dustdaniel]

Welcome to the MrExcel Message Board!

Cross-posting (posting the same question in more than one forum) is not against our rules, but the method of doing so is covered by #13 of the Forum Rules.

Be sure to follow & read the link at the end of the rule too!

Cross posted at:

Change pivot table cache | Microsoft Community Hub

Hi Everyone, A couple of years ago I found in a different forum, that you can change the cache ID from a pivot table, you just need 2 functions to do so...

techcommunity.microsoft.com

There is no need to repeat the link(s) provided above but if you have posted the question at other places, please provide links to those as well.

If you do cross-post in the future and also provide links, then there shouldn’t be a problem.

Sorry for that, I´ll make sure to avoid this next time.

 

[RoryA]

Please read the rule. We have no problem with you doing it, you just need to provide the links.

 

[dustdaniel]

You will need to have the right sheet active and put in your pivot table name
? ActiveSheet.PivotTables("PivotTable6").CacheIndex

Then without the "?" the below:
using the index no you got in the line before and using the right sheet and pivottable name
ActiveSheet.PivotTables("PivotTable1").CacheIndex = 1

Additional resource:

Excel Macros for Pivot Table Pivot Cache - Lists, Info

View pivot tables pivot cache information by using macros in this pivot table tutorial; see cacheindex, memory used, record count.

www.contextures.com

Thank you Alex for your reply.

I'm getting this error

I think I've found the one I used before:

?ActiveCell.PivotTable.cacheIndex
ActiveCell.PivotTable.cacheIndex = # (result from previous syntax)

For both cases I got the same error, I read about the error possible causes but I couldn´t get it fixed.

Any suggestion? Thanks in advance.

 

[dustdaniel]

Please read the rule. We have no problem with you doing it, you just need to provide the links.

I think that is the only other place where I posted the question, and I've read the rule # 13 and also the ExcelGuru article about cross posting. You mentioned there is no need to repeat the link, only to add if there is more than one. At this point nothing else to do right? I´m not used to post on forums but open to follow the rules..

I´ll make sure to post in the other site the solution once I found it.

Regards

 

[RoryA]

At this point nothing else to do right?

Correct. It was only because you said "I´ll make sure to avoid this next time" which gave me the impression you thought you weren't allowed to cross-post at all.