Changing slicer value

MarcFontyn

New Member
Joined
Apr 14, 2023
Messages
1
Office Version
  1. 365
Platform
  1. Windows
1681475280622.png

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
 

Excel Facts

Save Often
If you start asking yourself if now is a good time to save your Excel workbook, the answer is Yes
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
VBA Code:
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:
VBA Code:
    dim i as integer
    For i = 1 To sc1.SlicerItems.Count
        sc2.SlicerItems.Add (sc1.SlicerItems(i))
    Next i
 
Upvote 0

Forum statistics

Threads
1,223,214
Messages
6,170,772
Members
452,353
Latest member
strainu

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top