Synch slicers VBA - object defined error

[sashapixie]

Hello

I have two datasets:

Dataset 1	Contractor	Skill
Dataset 2	Contractor	Project

I have a slicer for both datasets for contractor and i have a slicer for skill.

What i want to do is when the skill slicer is selected which in turn updates the contractor column / slicer in the pivot table to then reflect that change in the second dataset which does not have skill.

I have been trying to use this original code from Mr Excel but i keep getting a 1004 object defined error on the line For Each SI1 In sc1.SlicerItems

Private Sub Worksheet_PivotTableUpdate(ByVal Target As PivotTable)

    'Declaring variables
    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_Contractor")
    Set sc2 = ThisWorkbook.SlicerCaches("Slicer_Contractor1")

    'make the macro faster
    Application.EnableEvents = False
    Application.ScreenUpdating = False

    'Clearing filters to mirror sc1
    sc2.ClearManualFilter

    'Getting error here
    For Each SI1 In sc1.SlicerItems
    sc2.SlicerItems(SI1.Name).Selected = SI1.Selected
    Next SI1

    'make the macro faster
    Application.EnableEvents = True
    Application.ScreenUpdating = True

End Sub

I have found so many forums with this solution but it seems to work for those and i cannot find anyone having the same error.

I would be really grateful for any help.

Thanks in advance.

 

[DanteAmor]

Works for me.
What version of excel do you have?

I suggest that you update your Account details (click your user name at the top right of the forum) so helpers always know what Excel version(s) & platform(s) you are using as the best solution often varies by version. (Don’t forget to scroll down & ‘Save’)

 

[sashapixie]

Works for me.
What version of excel do you have?

I suggest that you update your Account details (click your user name at the top right of the forum) so helpers always know what Excel version(s) & platform(s) you are using as the best solution often varies by version. (Don’t forget to scroll down & ‘Save’)

Hi DanteAmor

Thanks for your reply. I have updated my account information.

I am using Office 365.

 

[DanteAmor]

I tried to reproduce the scenario and the code works. I attach my test file to see if it works for you.
Just click on any Data (Data1, Data2, Data...) inside the slicer and automatically the other slicer is filtered.

varios 16dic2022 Slicer.xlsm

drive.google.com

----------------
If you have problems with your file you can upload a copy for someone to review and see what the problem is or find some other solution.

You could upload a copy of your file to a free site such www.dropbox.com or google drive. Once you do that, mark it for 'Sharing' and you will be given a link to the file that you can post here. If the workbook contains confidential information, you could replace it with generic data.