+ Reply to Thread
Results 1 to 8 of 8

Update Slicers with Date

  1. #1
    Registered User
    Join Date
    02-02-2018
    Location
    Canada
    MS-Off Ver
    2010
    Posts
    4

    Update Slicers with Date

    Hi all,

    I'm having a problem trying to update multiple slicers in Excel and with different date range and would like to ask for your expertise.

    First of all, there are three sheets where
    Sheet1 should pick a single day
    Sheet2 should pick Month-to-Date
    Sheet3 should pick Year-to-Date

    I record a macro to see how the code would look like when clicking a particular date in the slicer manually and here is the code:
    Please Login or Register  to view this content.
    My question is, is there a way to reference the date to a particular cell and then the macro will run all 3 sheets accordingly?

    Something similar to the code below where user will input the date in cell Q3:

    Please Login or Register  to view this content.
    Thank you so much!
    Last edited by Linda_1668; 02-02-2018 at 03:36 PM.

  2. #2
    Forum Expert CK76's Avatar
    Join Date
    06-16-2015
    Location
    ONT, Canada
    MS-Off Ver
    Office 365 ProPlus
    Posts
    5,775

    Re: Update Slicers with Date

    Please edit your post to nest code inside the tag (# button in the tool bar).

    Also, please upload a sample workbook (desensitized data), along with detailed explanation of what the code should do.
    I'm not sure I get your requirement.
    ?Progress isn't made by early risers. It's made by lazy men trying to find easier ways to do something.?
    ― Robert A. Heinlein

  3. #3
    Registered User
    Join Date
    02-02-2018
    Location
    Canada
    MS-Off Ver
    2010
    Posts
    4

    Re: Update Slicers with Date

    I have put the code tag.

    Sorry but I couldn't put a sample file cause it is a very large file with many slicers and pivot tables linking to external data source.

    However, I'm just wondering how can we change the date within the code (i.e. 20180130) to a cell reference so when a user input a different date, it will refresh the slicers with the date range relative to this date. For example, someone input 10/15/2017 in a cell, the macro would update the slicers as follow:

    Slicer in Sheet1: 20171015
    Slicer in Sheet2: 20171001 to 20171015
    Slicer in Sheet3: 20170101 to 20171015


    Thanks and sorry for not being clear.

  4. #4
    Forum Expert CK76's Avatar
    Join Date
    06-16-2015
    Location
    ONT, Canada
    MS-Off Ver
    Office 365 ProPlus
    Posts
    5,775

    Re: Update Slicers with Date

    Ok, I got idea of what you want to do. I'm headed out for the day but will look at it over the weekend.

  5. #5
    Registered User
    Join Date
    02-02-2018
    Location
    Canada
    MS-Off Ver
    2010
    Posts
    4

    Re: Update Slicers with Date

    thank you!

  6. #6
    Forum Expert CK76's Avatar
    Join Date
    06-16-2015
    Location
    ONT, Canada
    MS-Off Ver
    Office 365 ProPlus
    Posts
    5,775

    Re: Update Slicers with Date

    Here, try this. Tested and working on my end using OLAP based pivot & slicers.
    Please Login or Register  to view this content.
    Edit: Change the reference cell for dtPicked as needed.
    Last edited by CK76; 02-05-2018 at 11:34 AM. Reason: See Edit:

  7. #7
    Registered User
    Join Date
    02-02-2018
    Location
    Canada
    MS-Off Ver
    2010
    Posts
    4

    Re: Update Slicers with Date

    It worked! Thank you very much!

  8. #8
    Registered User
    Join Date
    07-01-2020
    Location
    Amsterdam
    MS-Off Ver
    2016
    Posts
    1

    Re: Update Slicers with Date

    Hi CK67, what if you only have "workdays" sliceritems, how to adjust the code above?

    For example, the sliceritems are 20200601~20200605, 20200608~20200612 etc

+ 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. Replies: 1
    Last Post: 10-18-2017, 03:24 PM
  2. VBA to set slicers to yesterdays date. What am I doing wrong?
    By beaglesBuddy in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 07-27-2017, 10:58 AM
  3. Can Pivot Table Slicers Auto Update ?
    By qualityexcel in forum Excel General
    Replies: 4
    Last Post: 04-07-2017, 06:38 PM
  4. Timeline does not update other slicers without refresh all being run
    By PFDave in forum Excel Programming / VBA / Macros
    Replies: 5
    Last Post: 01-31-2017, 01:32 PM
  5. Slicers - how to update one slicer for all charts?
    By kreinking in forum Excel Charting & Pivots
    Replies: 2
    Last Post: 09-06-2016, 03:35 PM
  6. Disconnect slicers, dynamically update pivot table data source range et reconnect
    By Vincent121 in forum Excel Programming / VBA / Macros
    Replies: 6
    Last Post: 06-10-2016, 05:30 AM
  7. [SOLVED] Disconnect Slicers, Change Pivot Table Source, Reconnect Slicers: a problem
    By Geoff. in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 07-30-2015, 02:27 PM

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