One Slicer to control 2 Pivot Tables with different data source

[Dokat]

Hi,

I have 2 pivot tables with different data sources and i am trying to control pvt tables with one slicer. I used power query to add the tables to data model. My issue is i cant connect 2 tables in power pivot as it doesnt let me create many to many relations. Did anyone come across a similar issue and solve for it.

Thank you

 

[Derek Brown]

Perhaps the following will help:
Controlling multiple pivot tables (separate cache) with one slicer | VBA & Macros | Excel Forum • My Online Training Hub

 

[Dokat]

Hi Derek,

I couldnt get it to work. I am getting run time error 91 message....i have 8 slicers and 2 pivot tablesbased on 2 different data source. My slicer names are category, brand, manufacturer. am i supposed to use Slicer_Category, Slicer_Manufacturer as slicer cache names or just category, brand manufacturer? also where do i put the pivot table name in the code.
Thank you for your help

 

[Derek Brown]

Please let me know the line on which the error is occurring.

 

[Dokat]

ADVERTISEMENT

Hi,

The error line is "For Each oSi In oScBrand1.SlicerItems"...Its Run Time Error '1004' Application-defined or object -defined error.

below is the code i am using



Dim mbNoEvent As Boolean


Private Sub Workbook_SheetPivotTableUpdate(ByVal Sh As Object, ByVal Target As PivotTable)
Dim oScCategory1 As SlicerCache
Dim oScManufacturer1 As SlicerCache
Dim oScBrand1 As SlicerCache
Dim oSc As SlicerCache
Dim oPT As PivotTable
Dim oSi As SlicerItem
Dim sBrand1 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 "*Brand1*" Then
Set oScBrand1 = oSc
ElseIf oSc.Name Like "*Category1*" Then
Set oScCategory1 = oSc
ElseIf oSc.Name Like "*Manufacturer1*" Then
Set oScManufacturer1 = oSc
End If
Exit For
End If
Next
If Not oScBrand1 Is Nothing And Not oScCategory1 Is Nothing And Not oScManufacturer1 Is Nothing Then Exit For
Next
If Not oScBrand1 Is Nothing Then
For Each oSc In ThisWorkbook.SlicerCaches
If Mid(oSc.Name, 7, 3) = Mid(oScBrand1.Name, 7, 3) And oSc.Name <> oScBrand1.Name Then
oSc.SlicerItems(oSc.SlicerItems.Count).Selected = True
For Each oSi In oScBrand1.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 oScManufacturer1 Is Nothing Then
For Each oSc In ThisWorkbook.SlicerCaches
If Mid(oSc.Name, 7, 3) = Mid(oScManufacturer1.Name, 7, 3) And oSc.Name <> oScManufacturer1.Name Then
oSc.SlicerItems(oSc.SlicerItems.Count).Selected = True
For Each oSi In oScManufacturer1.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 oScCategory1 Is Nothing Then
For Each oSc In ThisWorkbook.SlicerCaches
If Mid(oSc.Name, 7, 3) = Mid(oScCategory1.Name, 7, 3) And oSc.Name <> oScCategory1.Name Then
oSc.SlicerItems(oSc.SlicerItems.Count).Selected = True
For Each oSi In oScCategory1.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
End Sub

 

[Derek Brown]

The following link describes how to post VBA code:
http://www.mrexcel.com/forum/board-...post-your-visual-basic-applications-code.html
You need to debug the code to see what it is doing - for example, you need to make sure that the correct slicer names are being found etc.
I have added some "Debug.Print" statements to the first part of the code to give you an idea of what I mean. Te output will be seen in the Immediate window.

Dim mbNoEvent As Boolean
Private Sub Workbook_SheetPivotTableUpdate(ByVal Sh As Object, ByVal Target As PivotTable)
    Dim oScCategory1 As SlicerCache
    Dim oScManufacturer1 As SlicerCache
    Dim oScBrand1 As SlicerCache
    Dim oSc As SlicerCache
    Dim oPT As PivotTable
    Dim oSi As SlicerItem
    Dim sBrand1 As String
    Dim bUpdate As Boolean
    If mbNoEvent Then Exit Sub
    mbNoEvent = True
    bUpdate = Application.ScreenUpdating
    Application.ScreenUpdating = False
    ' DEBUG added ================================================================
    For Each oSc In ThisWorkbook.SlicerCaches
        Debug.Print "Processing SlicerCache: " & oSc.Name
        For Each oPT In oSc.PivotTables
            Debug.Print "Processing PivotTable: " & oPT.Name
            If oPT.Name = Target.Name And oPT.Parent.Name = Target.Parent.Name Then
                Debug.Print "PTs matched"
                If oSc.Name Like "*Brand1*" Then
                    Debug.Print "oScBrand1 set to: " & oSc.Name
                    Set oScBrand1 = oSc
                ElseIf oSc.Name Like "*Category1*" Then
                    Debug.Print "oScCategory1 set to: " & oSc.Name
                    Set oScCategory1 = oSc
                ElseIf oSc.Name Like "*Manufacturer1*" Then
                    Debug.Print "oScManufacturer1 set to: " & oSc.Name
                    Set oScManufacturer1 = oSc
                End If
                Exit For
            End If
        Next
        If Not oScBrand1 Is Nothing And Not oScCategory1 Is Nothing And Not oScManufacturer1 Is Nothing Then Exit For
    Next
    ' ===============================================================================
    If Not oScBrand1 Is Nothing Then
        For Each oSc In ThisWorkbook.SlicerCaches
            If Mid(oSc.Name, 7, 3) = Mid(oScBrand1.Name, 7, 3) And oSc.Name <> oScBrand1.Name Then
                oSc.SlicerItems(oSc.SlicerItems.Count).Selected = True
                ' Problem here =====>
                For Each oSi In oScBrand1.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 oScManufacturer1 Is Nothing Then
        For Each oSc In ThisWorkbook.SlicerCaches
            If Mid(oSc.Name, 7, 3) = Mid(oScManufacturer1.Name, 7, 3) And oSc.Name <> oScManufacturer1.Name Then
                oSc.SlicerItems(oSc.SlicerItems.Count).Selected = True
                For Each oSi In oScManufacturer1.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 oScCategory1 Is Nothing Then
        For Each oSc In ThisWorkbook.SlicerCaches
            If Mid(oSc.Name, 7, 3) = Mid(oScCategory1.Name, 7, 3) And oSc.Name <> oScCategory1.Name Then
                oSc.SlicerItems(oSc.SlicerItems.Count).Selected = True
                For Each oSi In oScCategory1.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
End Sub

 

[Dokat]

ADVERTISEMENT

Hi Derek,

I am still coming across the same issue error 1004 and following line is highlighted in yellow "For Each oSi In oScBrand1.SlicerItems". If it helps i can send you the file i am working on.

Thanks

 

[Derek Brown]

So the error has changed from 91 to 1004?
What details were output from the Debug statements?
Sorry, but I do not download other peoples workbooks. Two reasons, macro security and data protection.

 

[Dokat]

It just says... Run-time error '1004' Application-defined or object -defined error. Thanks

 

[Derek Brown]

Did you place that code in the ThisWorkbook module?
If you are not getting any output from the Debug.Print lines, then that means that is not finding any Slicer Caches.