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