Changing slicer value

[MarcFontyn]

Question :
Hello,

I would like to change the year 22 or 23 of the FiscalYear slicer and have the value 22 or 23 of the FiscalYear1 slicer change at the same time. At the moment when I change the value in the slicer_fiscalYear to 22 or 23 it works, but the value in the slicer_FiscalYear1 does not change.

Be careful if I change the value in the Slicer_FiscalYear1 it works (only for the Slicer_FiscalYear1).

I think I did the script correctly but I can't find why the 2nd FiscalYear value in the slicer_FiscalYear1 doesn't change at the same time. What did I forget?

• below my script :

Private Sub Worksheet_PivotTableUpdate(ByVal Target As PivotTable)

Dim sc1 As SlicerCache
Dim sc2 As SlicerCache
Dim SI1 As SlicerItem

' These names come from Slicer Settings dialog box
Set sc1 = ThisWorkbook.SlicerCaches("Slicer_FiscalYear")
Set sc2 = ThisWorkbook.SlicerCaches("Slicer_FiscalYear1")


Application.ScreenUpdating = False
Application.EnableEvents = False

sc2.ClearManualFilter


For Each SI1 In sc1.SlicerItems
sc2.SlicerItems(SI1.FiscalYear).Selected = SI1.Selected
Next SI1

Application.EnableEvents = True
Application.ScreenUpdating = True
End Sub

 

[sijpie]

Hi Marc,

welcome to the board. Please, if you post code, do it between code brackets. (click on the little VBA icon in the post toolbar, and paste your code)

I think this construct does not work

For Each SI1 In sc1.SlicerItems
    sc2.SlicerItems(SI1.FiscalYear).Selected = SI1.Selected
Next SI1

you set Sl1 to the sliceritems in sc1.
then you try to use this sliceritem in sc2. But sc2 is cleared, and anyway it would not have sc1's sliceritems.

Try the following instead:

    dim i as integer
    For i = 1 To sc1.SlicerItems.Count
        sc2.SlicerItems.Add (sc1.SlicerItems(i))
    Next i