1. Welcome to Chandoo.org Forums. Short message for you

    Hi Guest,

    Thanks for joining Chandoo.org forums. We are here to make you awesome in Excel. Before you post your first question, please read this short introduction guide. When posting or responding to questions please remember our values at Chandoo.org are: Humility, Passion, Fun, Awesomeness, Simplicity, Sharing Remember that we have people here for whom English is not there first language and we need to allow for this in our dealings.

    Yours,
    Chandoo
  2. 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...

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

Multiple slicers control 2 pivot table with 2 Data Source

Discussion in 'VBA Macros' started by Dokat, Dec 29, 2016.

  1. Dokat

    Dokat Member

    Messages:
    158
    Hi,

    I have 2 pivot tables from 2 different data sources...I am trying to synchronize multiple slicers to control both pivot tables.

    I used below code from "Chiriro" for single slicer to control two pivot tables. My challenge right now is how can i modify the code so that more than one slicer controls both pivot tables with different data source.

    Code (vb):

    PrivateSub Workbook_SheetPivotTableChangeSync(ByVal Sh AsObject, ByVal Target As PivotTable)

        Const MainSlicer AsString = "Slicer_Brand"
        Const Slicer1 AsString = "Slicer_Brand1"
       Dim pvt As PivotTable
       Dim ws As Worksheet
       ForEach ws In ThisWorkbook.Worksheets
           ForEach pvt In ws.PivotTables
                pvt.ManualUpdate = True
           Next pvt
       Next ws
     
       If Target.Name = "PivotTable1"Then

            Application.ScreenUpdating = False

            Application.EnableEvents = False

           OnErrorGoTo Catch

           Dim slMainSlicer As Slicer
           Dim slMainSlicercache As SlicerCache
           Dim slItem As SlicerItem

           Set slMainSlicercache = ThisWorkbook.SlicerCaches(MainSlicer)

            ActiveWorkbook.SlicerCaches(Slicer1).ClearManualFilter
       
           ForEach slItem In ThisWorkbook.SlicerCaches(Slicer1).SlicerItems
               If slMainSlicercache.SlicerItems(slItem.Name).Selected Then
                    slItem.Selected = True
               Else
                    slItem.Selected = False
               EndIf
           Next

       EndIf

    Catch:
        Application.EnableEvents = True
        Application.ScreenUpdating = True

       ForEach ws In ThisWorkbook.Worksheets
           ForEach pvt In ws.PivotTables
                pvt.ManualUpdate = False
           Next pvt
       Next ws
    EndSub

     
  2. Monty

    Monty Well-Known Member

    Messages:
    700
    @Docat

    Question is quite interesting...But what I feel is there is no code required to manage or control pivot tables.
  3. Dokat

    Dokat Member

    Messages:
    158
    vba needed because pivot tables are based on 2 different data sources.
  4. Monty

    Monty Well-Known Member

    Messages:
    700
    @Dokat.

    Okay agree with u.

    Sorry just giving u an idea rather then code as checking your question on mobile.

    Here what we can do....Change this below line in your code.

    If Target.Name = "PivotTable1" and Target.Name = "PivotTable2 "Then


    Hope this helps
  5. Dokat

    Dokat Member

    Messages:
    158
    Just tried it unfortunately it didnt work
  6. Chihiro

    Chihiro Well-Known Member

    Messages:
    3,153
    Upload small sample file representing your set up to get better help (and how each slicer should interact with each other).
  7. Dokat

    Dokat Member

    Messages:
    158
    Hi, i uploaded a sample file. Thank you for your help

    Attached Files:

  8. Chihiro

    Chihiro Well-Known Member

    Messages:
    3,153
    Don't use your actual file exported without underlying data. That's not helpful. As you can't make any modification to PivotTable without underlying data.

    Just build small sample table (25 rows or so) with appropriate fields for both your source table. Build Pivot & Slicer from it.
  9. Dokat

    Dokat Member

    Messages:
    158
    Please let me know if attached file works? Thank you

    Attached Files:

  10. Dokat

    Dokat Member

    Messages:
    158
    I used this code it works fine when all the items in the slicer caches matches in both Pivot table but gives error if both slicers dont have the exact items listed. Any way to work around this? Thanks

    Code (vb):
     
    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

     
  11. Chihiro

    Chihiro Well-Known Member

    Messages:
    3,153
    Upload sample that accurately represent your actual data and set up. Your uploaded sample does not meet the requirement.

    Also, please stop posting same question in multiple threads and starting new one. It makes it hard to follow and may waste someone's time if you jump from one to the other.
  12. Dokat

    Dokat Member

    Messages:
    158
    Hi,

    I attached the sample data source and the workbook that includes the pivot tables. Actual Source data includes 700 K rows of data. This would be the most accurate representation of original data source and the pivot tables. Please let me know if this meets your requirements?

    I am trying to control both pivot tables with same slicer. Thank you for your help

    Attached Files:

  13. Chihiro

    Chihiro Well-Known Member

    Messages:
    3,153
    Did you upload wrong workbook? Source isn't connected or related to pivot table generated...

    What I need is something that mirrors your workbook's set up. With enough data to demonstrate your issue (both the source and pivottable workbook).

    And you are now using PowerQuery? In that case, I'd just merge the two table, using concatenated fields that generates related fields that are unique (like [PrimarySKU] & some other column).

    At any rate, with PowerQuery and data being loaded to Data Model. You can leverage "VisibleSlicerItemsList". Instead of looping through each slicer items.

    See sample usage in below thread.
    http://chandoo.org/forum/threads/select-slicers-based-on-the-cell-values-excel-2013.30553/
  14. Dokat

    Dokat Member

    Messages:
    158
    Because of the size of the file i had to load it to Power Query rather than using external connections. I couldnt not merge the tables because its many to many relation ship. I will try to connect using concatenated fields and see if i can i get it to work. Actual dashboard is over 30 MB thus i created a sample file and uploaded only 25 rows data to see if its possible to connect slicers via vba.

    Thanks
  15. Chihiro

    Chihiro Well-Known Member

    Messages:
    3,153
    Hence, suggestion for concatenated field to generate unique key/ID that can be linked.

    Yes, but you need to have set up that represent your actual data. In your sample... it is missing underlying data. Just have source data in same workbook, instead of in separate workbook (for sample). It does not impact code.

    Also, you stated not all items are present in corresponding slicer to the other. Your sample has exact match between all items between corresponding slicers.
  16. Dokat

    Dokat Member

    Messages:
    158
    Ok i included the underlying data and added more sample data where it doesnt match all items between correspondings licers. I hope this works and what i am trying to achieve is doable.

    Thank you

    Attached Files:

  17. Dokat

    Dokat Member

    Messages:
    158
    I created concatenated field in both tables with primaryskubrand...however i keep getting below error message due to duplications.

    upload_2017-1-5_18-8-53.png
  18. Chihiro

    Chihiro Well-Known Member

    Messages:
    3,153
    In those instances you need third table as Lookup table and/or need 2 or more columns to serve as related column (SQL Join). Depending on how you are building relationship/query.

    However... Looking at your sample, I'm guessing that this data is brought in from database or some other source. Try and see if you can get your database people or software vendor, to build you "View" or query that will get you merged data set.
  19. Chihiro

    Chihiro Well-Known Member

    Messages:
    3,153
    As for the Code to control both pivots.

    What should happen if item in one slicer is selected and there's no corresponding item exist in the other? Since there isn't a way to select nothing from slicer...
  20. Dokat

    Dokat Member

    Messages:
    158
    It should just show the corresponding value in the slicer with selection.

    Below code partially works but it gets stuck where it says "oSc.SlicerItems(oSc.SlicerItems.Count).Selected = True" and keeps freezubf . I tested in smaller set of data and it worked fine but when i try with large data it freezes

    Code (vb):

    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
        Dim mbNoEvent As Boolean
        Dim ws As Worksheet
        For Each ws In ThisWorkbook.Worksheets
        For Each oPT In ws.PivotTables
            oPT.ManualUpdate = True
        Next oPT
    Next ws
       
        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
        For Each ws In ThisWorkbook.Worksheets
        For Each oPT In ws.PivotTables
            oPT.ManualUpdate = True
        Next oPT
    Next ws


    End Sub

     
  21. Chihiro

    Chihiro Well-Known Member

    Messages:
    3,153
    You didn't answer my question...
    Below example from your latest attachment.

    Form slicer:
    Has "LIQUID", "POWDER", "SHEETS", "UNIT DOSE".

    The other Form slicer:
    Does not have "SHEETS"

    So when "SHEETS" is selected in first Form slicer. What should happen? There is no corresponding item in the second...

    And what should happen if an item in second slicer is selected, but when there's not corresponding item in the first.
  22. Dokat

    Dokat Member

    Messages:
    158
    There are 2 separate pivot tables that's connected to the both slicers if there is a selection in one slicer and no corresponding value in second one...then table that is connected to the slicer with selection shows data and the other one preferable show 0 value. Thanks
  23. Chihiro

    Chihiro Well-Known Member

    Messages:
    3,153
    Not possible without adding data to source table. Hence my question...
  24. Dokat

    Dokat Member

    Messages:
    158
    Thanks for trying

Share This Page