Automatically filtering the dates on the slicers for the OLAP workbook RRS feed

  • Question

  • I have created an OLAP workbook (excel 2013 version) with many pivot tables and graphs with Date Hierarchy Slicers (four slicers - Current MTD, Previous year MTD,  Year to date, previous year-to-date).

    And I am trying to automate the manual process selecting the dates on the slicers to the latest available day when I open the workbook.

    Slicer Names:  "CY Day", "PY Day", "CY Day YTD" and "PY Day YTD"

    For example,

    Let's say that today is Aug 24, 2016.

    If I open the workbook on Dec 30, 2016, then I would like the dates on the slicers to be automatically updates as following:

    CY Day: 12/1/2016 - 12/30/2016

    PY Day: 12/1/2015 - 12/30/2015

    CY Day YTD: 1/1/2016 - 12/30/2016

    PY Day YTD: 1/1/2015 - 12/30/2015

    What would be the VBA to make this work?

    Wednesday, August 24, 2016 8:34 PM


  • Hi thakgung2,

    According to your description, as far as I know that we are able to access the SlicerItem object then set SlicerItem.Selected property to True or False. True if the slicer item is selected; otherwise False.
    But this property is read-only for slicers connected to OLAP data sources.

    In addition if you have any feedback for Excel, please feel free to submit them to User Voice:

    Thanks for your understanding.
    • Proposed as answer by David_JunFeng Friday, September 2, 2016 8:21 AM
    • Marked as answer by David_JunFeng Monday, September 5, 2016 5:27 AM
    Thursday, August 25, 2016 6:03 AM