VBA disconnect/connect slicers in active worksheet

[Vidar]

Hi
Sometimes I need to add an extra column to my pivot table by changing the Data Source.
But Excel prompts me to first disconnect my slicers since I have two or more pivottables
on the same worksheet sharing the same pivotcache.

Tried to use the macro recorder to figure out a way to loop through each slicer
and disconnect every pivottable from the slicer.
I'm not familiar with the pivottable/slicer VBA objects.

Vidar

 

[Jerry Sullivan]

I think you'll be better off using the new code. That way if you reuse it or add more Pivots you don't have to remember to change the names.

You can only edit your post for 10 minutes after posting. After that the Edit button goes away.

 

[DutchArjo]

unfortuenately still no success: after using the new code, I receive this error:

-2147024809: this is an invalid reference (or which word Excel might use for this)

 

[Jerry Sullivan]

It's hard to guess what the problem might be without seeing a file that exhibits that repeatable behavior.
I'd be glad to take a look at your file with any confidential data removed/changed.

You can upload it to a hosting site like Box.com and post a link or send me a PM and we'll exchange email addresses.

 

[Jerry Sullivan]

Hi Arjo, Thanks for sending your file. I think that error might be related to an Add-In you are using.
I wasn't able to compile the code at all due to a missing library reference to NITRO.xlam.

I unchecked that reference and deleted all the code in Module1 except for the "change slicers" code.
After doing that, the change slicers code ran without error and appeared to work. The pivots were remapped to use Tabel3 as their data source.
I used your code from Post #17 to confirm that the mapping of Slicer connections matched before and after running.

Perhaps you could temporarily remove the reference to NITRO.xlam then remap your slicers. That should work since this is a one-time fix and the dynamic tables won't require changing of the data sources.

 

[DutchArjo]

thanks for your help. Nitro is a Nielsen (www.nielsen.com) excel add-on to use marketdata databases in Excel. After you posted your latest reply I just disconnected all slicers, changed sources and then reconnected all slicers manually for the files I already had. It was a lot of work but now it's done and the files are working.

 

[Jerry Sullivan]

Oh, sorry to hear you ended up doing that manually. The macro worked for me when the reference to Nitro was temporarily removed.

Well, I learned some things in the process, and hopefully the code above will be of use to others that want to remap Pivots connected to Slicers to use dynamic data sources.

Cheers Arjo!

 

[DutchArjo]

it's ok. I just needed to finish the files and I would not ask you again for another script to temporarily remove the nitro reference

I now also learned it would be better for me to have 1 xlam file with all my needed code in it and link every file to this xlam file. This way I only have to adjust the code at one place which costs my less time then when I have to adjust the code in about 20 files or more.

Now I have to find out how to do this.

Is there a thanks / props button or whatever I can push about 1 zillion times to thank you for your help?

 

[Jerry Sullivan]

No buttons needed- your thanks is thanks enough!

Jan Karel Pieterse's site has a nice introduction to creating add-ins:
Create Addins

 

[dkmanley]

Hi Jerry,

I'm replying here because I would like to disconnect slicers but only in the active worksheet. I have 8 worksheets and 2 or more pivot tables per sheet with 4 or more slicers per pivot table and I need to change source data in a few.
For example in one sheet there is one PT and 3 slicers with the following slicer cache:

Slicer_Super_Region
Slicer_Contract_Status
Slicer_Doc_Type3

Do you have to specify the above for each slicer? Thanks. Dave

 

[Jerry Sullivan]

Hi Dave and Welcome to MrExcel,

The recent code examples dealt with the problem of disconnecting pivots from slicers, changing the data source, then reconnecting the pivots and slicers.

It sounds like you just want disconnect the pivots on the ActiveSheet from specified slicers without the need to reconnect. Is that correct?

If so, for just a single slicer cache....

Sub DisconnectSlicerPivots1()
'--disonnects pivots on ActiveSheet from specified
'  slicerCache

 Dim pvt As PivotTable
 Dim sSheetName As String

 sSheetName = ActiveSheet.Name
 
 With ActiveWorkbook.SlicerCaches("Slicer_Super_Region")
   For Each pvt In .PivotTables
      If pvt.Parent.Name = sSheetName Then
        .PivotTables.RemovePivotTable (pvt)
      End If
   Next pvt
 End With
End Sub

For each slicer cache in a list...

Sub DisconnectSlicerPivots2()
'--disonnects pivots on ActiveSheet from each
'  slicerCache specified in a list

 Dim lNdx As Long
 Dim pvt As PivotTable
 Dim sSheetName As String
 Dim vSlicerCacheNames As Variant
 
 vSlicerCacheNames = Array("Slicer_Super_Region", _
   "Slicer_Contract_Status", "Slicer_Doc_Type3")

 sSheetName = ActiveSheet.Name
 
 For lNdx = LBound(vSlicerCacheNames) To UBound(vSlicerCacheNames)
   With ActiveWorkbook.SlicerCaches(vSlicerCacheNames(lNdx))
     For Each pvt In .PivotTables
        If pvt.Parent.Name = sSheetName Then
          .PivotTables.RemovePivotTable (pvt)
        End If
     Next pvt
   End With
 Next lNdx
End Sub