All pivot tables with OLAP Cube data sources refreshing instead of specified pivot

[RThomas05]

Hi All,

First post but let me thank the board for all the knowledge i've gained from this board, I'm self taught with VBA and have come a long way with the help from this great resource! My question is somewhat of the opposite of what most users are asking for which is why i started this thread. I have an excel file with 40+ pivot tables some are OLAP Cube sourced data others are O'Data. I have a tab that lists all the pivot tables in the file and has a column next to each pivot table name that has drop downs of yes/no. My macro moves through each of those drop downs and then refreshes the specific pivot based on those selections. Recently i've noticed that a table that i have marked as "no" has been changing. Upon investigation i've realized that when one OLAP Cube sourced pivot is refreshed it refreshes all OLAP pivots. This could be helpful for some but it is the opposite for me as i want those "no" specified tables to keep their historic data, in this situation it is a pivot for the month of April which is long past. Here is the part of my code that i use to refresh the tables marked "yes".

If Cells(i, 15) = "Yes" Then Sheet = "" & Cells(i, 8) & ""
Table = Cells(i, 9)
Set pt = Sheets(Sheet).PivotTables(Table)
.RefreshTable

There is more to the code but for discussion sake that is all that i think applies for my question. It then goes onto next i sort of thing and continues down the list. Does anyone know if what i'm looking to do is possible? If so any suggestions would be very helpful as this has been a pain in my butt for the last few weeks. I really appreciate any help.

Thanks,

Rob

 

[mole999]

I would make sure all my tables have a different name, then refer to them by name

 

[SpillerBD]

Question? You have one data source (OLAP) connection that feeds into multiple pivot tables?

 

[RThomas05]

They all have unique names thanks for the reply though.

 

[RThomas05]

That is correct.

 

[RThomas05]

I'm looking into this currently "unshare a data cache between pivot tables" I'll share if that resolves my problem

 

[RThomas05]

Question? You have one data source (OLAP) connection that feeds into multiple pivot tables?

sorry i replied on my phone and it didn't reply quoting your question. But yes it is one data feed.

 

[SpillerBD]

sorry i replied on my phone and it didn't reply quoting your question. But yes it is one data feed.

I half figured as much.
Didn't see need to comment since it sounded like a good lead. And if you're that far with the VBA, any modification shouldn't be too much.

For those interested, here's the Microsoft article link. https://support.office.com/en-in/article/unshare-a-data-cache-between-pivottable-reports-87188806-0c24-4d17-b2f7-9e3a4a05542b

 

[SpillerBD]

I'm looking into this currently "unshare a data cache between pivot tables" I'll share if that resolves my problem

sooo.... How'd it go?