+ Reply to Thread
Results 1 to 6 of 6

synchronise slicers through different data sets with not 100% matching slicer items

  1. #1
    Registered User
    Join Date
    05-01-2020
    Location
    Netherlands
    MS-Off Ver
    365
    Posts
    3

    synchronise slicers through different data sets with not 100% matching slicer items

    Hi all,

    I'm working on a rather large template file that generates graphs, tables and calculated values from several data sets.
    I have performed several steps to do so, and they all work:
    • Attach several datasources to excel (mostly existing excel tables)
    • Make pivot tables and slicers
    • Synchronise the slicers throughout the different data sources
    • Generate needed graphs, tables and calculations for large number of reports to be generated
    • Loop through slicer ietms to update tables and graphs
    • make copy of word template, polulate it with the graphs, tables and calculations
    • save the file under specific name.


    Now i'm stuck on a issue:


    I want to synchronise slicers through different data sets with not 100% matching slicer items

    In this example i have 2 datasets, with pivottables ans slicers.

    Knipsel.PNG


    First dataset has 6 names and 6 surnames in it (and so does the pivottable and slicer)
    Second dataset has 5 names and 5 surnames in it

    If the sliceritem in the first dataset is missing in the second dataset it can't be selected and ideally, no sliceritems should be selected in the second.
    However, pivottables demand for at least one sliceritem to be selected.

    I therefor made a 6th name in the second dataset, and named it DUMMY.
    So now each time the non existing item of slicer 1 in slicer 2 is selected it SHOULD select the dummy item, however it results still in selecting all items of the second data set

    Knipsel1.PNG

    Below the code:

    Private Sub Worksheet_PivotTableUpdate(ByVal Target As PivotTable)
    Dim sc1 As SlicerCache
    Dim sc2 As SlicerCache
    Dim si1 As SlicerItem
    Dim si2 As SlicerItem

    Set sc1 = ThisWorkbook.SlicerCaches("Slicer_Name")
    Set sc2 = ThisWorkbook.SlicerCaches("Slicer_Name1")

    Application.ScreenUpdating = False
    Application.EnableEvents = False

    sc2.ClearManualFilter


    On Error Resume Next
    For Each si2 In sc2.SlicerItems
    Set si1 = sc1.SlicerItems(si2.Name)
    If Not si1 Is Nothing Then
    si2.Selected = si1.Selected
    Else
    si2.Selected = False
    End If
    Next si2
    On Error GoTo 0

    Application.EnableEvents = True
    Application.ScreenUpdating = True
    End Sub



    I hope someone can help me out here
    Attached Files Attached Files

  2. #2
    Valued Forum Contributor
    Join Date
    01-14-2013
    Location
    Austria
    MS-Off Ver
    2016 / 2019
    Posts
    339

    Re: synchronise slicers through different data sets with not 100% matching slicer items

    some small changes
    if there are also items which are not in slicer2 it works and always sets "Dummy" as only one selected
    The only which is not supported up to now is if the "Dummy is the last item in the slicer and no one is selected up to now. This check is missing

    Please Login or Register  to view this content.
    cheers
    Attached Files Attached Files

  3. #3
    Registered User
    Join Date
    05-01-2020
    Location
    Netherlands
    MS-Off Ver
    365
    Posts
    3

    Re: synchronise slicers through different data sets with not 100% matching slicer items

    Gue2013,

    Thank you, it seems to work as desired.

    I have expanded the code for more datasets slicers as a test, and this is also working.
    In reality i can expect up to 20 different datasets and i can at least copy the code for each additional slicer as i did in the updated example file.

    Knipsel3.PNG


    I wonder if this can be made easier also, but that is not very important in this moment.

    Thx again
    Attached Files Attached Files

  4. #4
    Valued Forum Contributor
    Join Date
    01-14-2013
    Location
    Austria
    MS-Off Ver
    2016 / 2019
    Posts
    339

    Re: synchronise slicers through different data sets with not 100% matching slicer items

    using a loop ?
    Please Login or Register  to view this content.
    you can also loop through all slicers on the worksheet except the master (independent of number of slicers)
    cheers

  5. #5
    Registered User
    Join Date
    05-01-2020
    Location
    Netherlands
    MS-Off Ver
    365
    Posts
    3

    Re: synchronise slicers through different data sets with not 100% matching slicer items

    Hi Gue2013,

    Somehow my last message wasn't send, but the loop you suggested works like a charm. Thanks againn.

  6. #6
    Valued Forum Contributor
    Join Date
    01-14-2013
    Location
    Austria
    MS-Off Ver
    2016 / 2019
    Posts
    339

    Re: synchronise slicers through different data sets with not 100% matching slicer items

    thanks for the feedback - nice to hear that

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. Show value of highest slicer selections (multi selected items in slicer
    By saudi_red_neck in forum Excel Charting & Pivots
    Replies: 0
    Last Post: 03-01-2020, 05:37 AM
  2. Exclude items from chart slicers (even if they have data)
    By chicagolarsons in forum Excel Charting & Pivots
    Replies: 4
    Last Post: 01-01-2019, 01:24 PM
  3. [SOLVED] Line Graph With 2 Sets Of Data and Slicer
    By Gtrtim112 in forum Excel Charting & Pivots
    Replies: 10
    Last Post: 08-29-2018, 05:43 PM
  4. Connecting slicers from different data sets using table format (not by creating pivot)
    By karthikgmk in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 11-17-2016, 01:37 PM
  5. Connecting slicers from different data sets using table format (not by creating pivot)
    By karthikgmk in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 11-17-2016, 12:36 PM
  6. VBA Code for multiple Slicers from different data sets
    By Pinky121 in forum Excel Programming / VBA / Macros
    Replies: 5
    Last Post: 07-02-2015, 12:02 AM
  7. [SOLVED] Pivot Slicers from multiple Caches - problem with 2nd Slicer displaying incorrect data
    By matt4003 in forum Excel Programming / VBA / Macros
    Replies: 9
    Last Post: 01-14-2013, 03:06 AM

Tags for this Thread

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1