none
Automatically select last available date slicer value when opening workbook RRS feed

  • Question

  • Hi,

    What I'd like to do is to open an Excel workbook and get my pivot table automatically filtered by the last available day in a slicer.

    Lets say I open my workbook today (09/23/2016): I want my pivot tables to get filtered by my date slicer with today's date (automatically). So instead of clicking by my own on the slicer day (09/23/2016), I want somehow to achieve the action automatically.

    Thank you

    Friday, September 23, 2016 2:30 PM

Answers

  • Hi,

    Please try code below

    Private Sub Workbook_Open()
        Sheets("SheetName").PivotTables("PivotTableName").PivotFields("DateFieldName").PivotFilters. _
            Add2 Type:=xlDateToday
    End Sub

    Saturday, September 24, 2016 2:42 PM
    Moderator

All replies

  • Hi,

    What I'd like to do is to open an Excel workbook and get my pivot table automatically filtered by the last available day in a slicer.

    Lets say I open my workbook today (09/23/2016): I want my pivot tables to get filtered by my date slicer with today's date (automatically). So instead of clicking by my own on the slicer day (09/23/2016), I want somehow to achieve the action automatically.

    Thank you 

    Friday, September 23, 2016 2:28 PM
  • Hi,

    Please try code below

    Private Sub Workbook_Open()
        Sheets("SheetName").PivotTables("PivotTableName").PivotFields("DateFieldName").PivotFilters. _
            Add2 Type:=xlDateToday
    End Sub

    Saturday, September 24, 2016 2:42 PM
    Moderator
  • Thank you for your replay 

    I'm getting the application defined or object defined error

     

    Saturday, September 24, 2016 2:55 PM
  • Hi,

    If there is a date type field in your PivotTable or not?

    Please Check if there is Date Filters for this PivotField.

    Saturday, September 24, 2016 3:06 PM
    Moderator