• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Connecting Slicers from Different Data Sources

D

Dokat

Member
Hi,

I am using below VBA code to control 2 slicers from 2 Pivot table with 2 different data sources. However when i make a selection in slicer i am receiving error message 1004 error message. My Slicer names are Category, Brand and Manufacturer. Can anyone please help me wfind out whats causing the problem.


Code: 
Dim mbNoEvent As Boolean

Private Sub Workbook_SheetPivotTableUpdate(ByVal Sh As Object, ByVal Target As PivotTable)
    Dim oScSlicer_Brand As SlicerCache
    Dim oScSlicer_Manufacturer As SlicerCache
    Dim oScSlicer_Category As SlicerCache
    Dim oSc As SlicerCache
    Dim oPT As PivotTable
    Dim oSi As SlicerItem

    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 "*Slicer_Category*" Then
                    Set oScSlicer_Category = oSc
                ElseIf oSc.Name Like "*Slicer_Brand*" Then
                    Set oScSlicer_Brand = oSc
                ElseIf oSc.Name Like "*Slicer_Manufacturer*" Then
                    Set oScSlicer_Manufacturer = oSc
                End If
                Exit For
            End If
        Next
        If Not oScSlicer_Category Is Nothing And Not oScSlicer_Brand Is Nothing And Not oScSlicer_Manufacturer Is Nothing Then Exit For
    Next
    If Not oScSlicer_Category Is Nothing Then
        For Each oSc In ThisWorkbook.SlicerCaches
            If Mid(oSc.Name, 7, 3) = Mid(oScSlicer_Category.Name, 7, 3) And oSc.Name <> oScSlicer_Category.Name Then
                oSc.SlicerItems(oSc.SlicerItems.Count).Selected = True
                For Each oSi In oScSlicer_Category.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 oScSlicer_Manufacturer Is Nothing Then
        For Each oSc In ThisWorkbook.SlicerCaches
            If Mid(oSc.Name, 7, 3) = Mid(oScSlicer_Manufacturer.Name, 7, 3) And oSc.Name <> oScSlicer_Manufacturer.Name Then
                oSc.SlicerItems(oSc.SlicerItems.Count).Selected = True
                For Each oSi In oScSlicer_Manufacturer.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 oScSlicer_Brand Is Nothing Then
        For Each oSc In ThisWorkbook.SlicerCaches
            If Mid(oSc.Name, 7, 3) = Mid(oScSlicer_Brand.Name, 7, 3) And oSc.Name <> oScSlicer_Brand.Name Then
                oSc.SlicerItems(oSc.SlicerItems.Count).Selected = True
                For Each oSi In oScSlicer_Brand.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
 
Hello Dokat..

Great you could found solution...It's always better to share in the forum which can help some body..Keep learning..

Thank you.
 
You must log in or register to reply here.
Back
Top