Disconnect Slicers, Change Pivot Table Source, Reconnect Slicers: a problem

[Geoff.]

Hello,

I have run into the problem of not being able to programmatically do the following: a) disconnect pivot table slicers; b) change pivot table source (different workbooks); c) reconnect slicers.

I need this because depending on what users want to see, different pivot table data needs to be loaded but I can't change the pivot table source without disconnecting the slicers first.

All pivot tables in this workbook have the same pivot cache.
There are 4 slicers ("Slicer_Office", "Slicer_Week", "Slicer_Name", "Slicer_Name1"), 2 of which connect to tall pivot tables in the wb, and 2 of which connect to different tables.

I've looked elsewhere in the forum and have found something that looks like it should work but I only get errors when I try to run it.

Here is the most promising code I have found so far: Excel forum thread

Here is how Izandol's code looks when I try to adapt it for my use (but I get an error):

Please Login or Register  to view this content.

So far I get an "Run-time error '5': Invalid procedure call or argument" error at line:

Please Login or Register  to view this content.

Please help me,

Geoff.

[romperstomper]

You've already included 'Slicer_' in the names in the array so you don't need to add it again. It should be:

Please Login or Register  to view this content.

[Geoff.]

Hmm, that was obvious.. thanks!

I was hoping that was the only error but...
Any chance you spot why I get the "reference not valid error" at this line?:

Please Login or Register  to view this content.

Do you think its this part: "SourceData:=sNewSource?" In the original post where I found this code, it was like this:

Please Login or Register  to view this content.

Here is the code in full again (with the 'string_' removed):

Please Login or Register  to view this content.

[Geoff.]

So I patched up something that works after trying a few things out with more code I found online. The working code is below.

I have run into a new problem, this codes disconnects all slicers from all pivot tables and reconnects all slicers to all pivot tables; in my case, not all my slicers (I have 4 of them) connect to all my pivot tables so I need to figure out to select the pivot tables that each slicer connects to individually. I will ask this question in a new post if I can't figure it out myself.

Here's what it looks like and it does work although it is fragmented and can surely be simplified by someone more adept than I with VBA:

Please Login or Register  to view this content.