VBA Slicer Code For Power Pivot Data Model Based Pivot Tables

[MercuryVBA]

Hello,

I am trying to write some code to synchronize two slicers that are linked to pivot tables that were created from a Power Pivot Data Model. I need to do this in the first place, because the data model is not properly working to control all of the tables with defined relationships properly.

The below code is a sample I got from Bill Jelen's channel and works for slicers created from regular pivot tables.

But when running it for Slicers created from Power Pivot Data Model pivot tables, it fails at sc1.SlicerItems and returns a 1004 runtime error.

Does anyone have any idea how to get around this? Thank you so very much

Private Sub Workbook_SheetPivotTableUpdate(ByVal Sh As Object, ByVal Target As PivotTable)
                
    Dim sc1 As SlicerCache
    Dim sc2 As SlicerCache
    Dim SI1 As SlicerItem


    Set sc1 = ThisWorkbook.SlicerCaches("Slicer_Name")
    Set sc2 = ThisWorkbook.SlicerCaches("Slicer_Name2")
    
    Application.ScreenUpdating = False
    Application.EnableEvents = False


    sc2.ClearManualFilter
    
    'Fails on this next line of code for Slicers created from Data Model Pivot Tables
    For Each SI1 In sc1.SlicerItems
        sc2.SlicerItems(ST1.Name).Selected = ST1.Selected
    Next SI1


    Application.EnableEvents = True
    Application.ScreenUpdating = True


End Sub

 

[Matt Allington]

In my experience, it is possible to have 2 slicers that are clones. I don't know what causes cloning (same name) vs duplicating (different names) but both definitely can occur. I would try copy and paste a the slicer on the same page. Check the names. If they are tthe same, cut and paste one to the new location and then recheck.

 

[SliceNdiceUup]

Hello,

I am trying to write some code to synchronize two slicers that are linked to pivot tables that were created from a Power Pivot Data Model. I need to do this in the first place, because the data model is not properly working to control all of the tables with defined relationships properly.

The below code is a sample I got from Bill Jelen's channel and works for slicers created from regular pivot tables.

But when running it for Slicers created from Power Pivot Data Model pivot tables, it fails at sc1.SlicerItems and returns a 1004 runtime error.

Does anyone have any idea how to get around this? Thank you so very much

Private Sub Workbook_SheetPivotTableUpdate(ByVal Sh As Object, ByVal Target As PivotTable)
               
    Dim sc1 As SlicerCache
    Dim sc2 As SlicerCache
    Dim SI1 As SlicerItem


    Set sc1 = ThisWorkbook.SlicerCaches("Slicer_Name")
    Set sc2 = ThisWorkbook.SlicerCaches("Slicer_Name2")
   
    Application.ScreenUpdating = False
    Application.EnableEvents = False


    sc2.ClearManualFilter
   
    'Fails on this next line of code for Slicers created from Data Model Pivot Tables
    For Each SI1 In sc1.SlicerItems
        sc2.SlicerItems(ST1.Name).Selected = ST1.Selected
    Next SI1


    Application.EnableEvents = True
    Application.ScreenUpdating = True


End Sub

I am looking for the same solution, transferring slicer content between 2 slicers referring to the same Power Pivot datamodel. Any solution would be greatly appreciated. Thanks

 

[hakanfa]

I am looking for the same solution, transferring slicer content between 2 slicers referring to the same Power Pivot datamodel. Any solution would be greatly appreciated. Thanks

Hi, I am experiencing same problem and looking for same solutions as you do, have you found any working solution to this issue?

Br,
Hakan