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