Connecting multiple slicers to multiple pivot tables

[Stilesv74]

I have three pivot tables in a workbook, and three slicers on one page next to one of the pivot tables. The pivot tables are all linked to the same data source. The three slicers are linked to the same fields in all three pivot tables (Product, Project & Supplier). I need to connect the slicers to the other two pivot tables. So if someone selects a product on the product slicer in sheet2, then it will filter sheet3 & sheet4.

Sheet1: Has the data
Sheet2 "Project": has a pivot table called ProjectPT and the three slicers (Project, Product & Supplier)
Sheet3 "Supplier": has a pivot table called SupplierPT
Sheet4 "Product": has a pivot table called ProductPT

Here is the code I have written for the slicer report connection. It is giving me a run-time error "424" Object Required on the bold row.

   Dim oScProduct As SlicerCache
    Dim oScProject As SlicerCache
    Dim oScSupplier As SlicerCache
    Dim oSc As SlicerCache
    Dim oPT As PivotTable
    Dim oSi As SlicerItem
    Dim sSupplier As String
    Dim bUpdate As Boolean
 
    If mbNoEvent Then Exit Sub
    mbNoEvent = True
    bUpdate = Application.ScreenUpdating
    Application.ScreenUpdating = False
    For Each oSc In ThisWorkbook.SlicerCaches
        For Each oPT In oSc.PivotTables
            If oPT.Name = Target.Name And oPT.Parent.Name = Target.Parent.Name Then
                If oSc.Name Like "*Supplier*" Then
                    Set oScSupplier = oSc
                ElseIf oSc.Name Like "*Product*" Then
                    Set oScProduct = oSc
                ElseIf oSc.Name Like "*Supplier*" Then
                    Set oScSupplier = oSc
                End If
                Exit For
            End If
        Next
        If Not oScSupplier Is Nothing And Not oScProduct Is Nothing And Not oScProject Is Nothing Then Exit For
    Next
    If Not oScSupplier Is Nothing Then
        For Each oSc In ThisWorkbook.SlicerCaches
            If Mid(oSc.Name, 7, 3) = Mid(oScSupplier.Name, 7, 3) And oSc.Name <> oScSupplier.Name Then
               
                oSc.SlicerItems(oSc.SlicerItems.Count).Selected = True
                For Each oSi In oScSupplier.SlicerItems
                    On Error Resume Next
                    If oSc.SlicerItems(oSi.Value).Selected <> oSi.Selected Then
                        oSc.SlicerItems(oSi.Value).Selected = oSi.Selected
                    End If
                Next
            End If
        Next
    End If
    If Not oScProject Is Nothing Then
        For Each oSc In ThisWorkbook.SlicerCaches
            If Mid(oSc.Name, 7, 3) = Mid(oScProject.Name, 7, 3) And oSc.Name <> oScProject.Name Then
                oSc.SlicerItems(oSc.SlicerItems.Count).Selected = True
                For Each oSi In oScProject.SlicerItems
                    On Error Resume Next
                    If oSc.SlicerItems(oSi.Value).Selected <> oSi.Selected Then
                        oSc.SlicerItems(oSi.Value).Selected = oSi.Selected
                    End If
                Next
            End If
        Next
    End If
    If Not oScProduct Is Nothing Then
        For Each oSc In ThisWorkbook.SlicerCaches
            If Mid(oSc.Name, 7, 3) = Mid(oScProduct.Name, 7, 3) And oSc.Name <> oScProduct.Name Then
                oSc.SlicerItems(oSc.SlicerItems.Count).Selected = True
                For Each oSi In oScProduct.SlicerItems
                    On Error Resume Next
                    If oSc.SlicerItems(oSi.Value).Selected <> oSi.Selected Then
                        oSc.SlicerItems(oSi.Value).Selected = oSi.Selected
                    End If
                Next
            End If
        Next
    End If
    mbNoEvent = False
    Application.ScreenUpdating = bUpdate

Thank you!

 

[SpillerBD]

Did you record making the slicers connection to other pivots to begin your code?
Right click slicer, select "Report connections"

 

[Stilesv74]

Yes, I did record making the slicers connection but when I run that macro I get the following error: Run-Time error '1004': Unable to get the PivotTables property of the Worksheet class.

Here is the code when I record making the slicer connection:

    ActiveWorkbook.SlicerCaches("Slicer_Product_GN_Reference").PivotTables. _
        AddPivotTable (ActiveSheet.PivotTables("ProductPT"))
    ActiveWorkbook.SlicerCaches("Slicer_Product_GN_Reference").PivotTables. _
        AddPivotTable (ActiveSheet.PivotTables("SupplierPT"))
    ActiveSheet.Shapes.Range(Array("Project GN Reference")).Select
    ActiveWorkbook.SlicerCaches("Slicer_Project_GN_Reference").PivotTables. _
        AddPivotTable (ActiveSheet.PivotTables("ProductPT"))
    ActiveWorkbook.SlicerCaches("Slicer_Project_GN_Reference").PivotTables. _
        AddPivotTable (ActiveSheet.PivotTables("SupplierPT"))
    ActiveSheet.Shapes.Range(Array("Supplier")).Select
    ActiveWorkbook.SlicerCaches("Slicer_Supplier").PivotTables.AddPivotTable ( _
        ActiveSheet.PivotTables("ProductPT"))
    ActiveWorkbook.SlicerCaches("Slicer_Supplier").PivotTables.AddPivotTable ( _
        ActiveSheet.PivotTables("SupplierPT"))

 

[Domenic]

Try...

    [COLOR=darkblue]With[/COLOR] ActiveWorkbook.SlicerCaches("Slicer_Product_GN_Reference").PivotTables
        .AddPivotTable Worksheets("Sheet3").PivotTables("SupplierPT")
        .AddPivotTable Worksheets("Sheet4").PivotTables("ProductPT")
    [COLOR=darkblue]End[/COLOR] [COLOR=darkblue]With[/COLOR]
        
    [COLOR=darkblue]With[/COLOR] ActiveWorkbook.SlicerCaches("Slicer_Supplier").PivotTables
        .AddPivotTable Worksheets("Sheet3").PivotTables("SupplierPT")
        .AddPivotTable Worksheets("Sheet4").PivotTables("ProductPT")
    [COLOR=darkblue]End[/COLOR] [COLOR=darkblue]With[/COLOR]

Hope this helps!

 

[Stilesv74]

It works!!! I would give you a hug if I could! Thank you so much!!!