Select first item in slicer with OLAP pivot source

Zenaida

New Member
Joined
May 23, 2017
Messages
6
Office Version
  1. 365
Platform
  1. Windows
How do I select the first item in either the slicer or the pivot table? This is a date field and both the pivot table and slicer are sorted by descending so the newest date is always listed first. The below code works if I always want to select October 27, 2020; however, I need the most recent date selected. Because of how I have my pivot table/slicer sorted, the newest date should always be listed first. Code that will either select the first item or select the newest date will work. The field is formatted as a date, not date/time.

VBA Code:
ActiveWorkbook.SlicerCaches("Slicer_Dates").VisibleSlicerItemsList = Array("[tblDates].[Dates].&[2020-10-27T00:00:00]")

I appreciate any assistance you can provide. Thank you.

----------------------------------------
Office 365 Business
 

Some videos you may like

Excel Facts

Do you hate GETPIVOTDATA?
Prevent GETPIVOTDATA. Select inside a PivotTable. In the Analyze tab of the ribbon, open the dropown next to Options and turn it off

Zenaida

New Member
Joined
May 23, 2017
Messages
6
Office Version
  1. 365
Platform
  1. Windows
Figured out how to do it:

VBA Code:
Dim MyReportDate As String
Dim LocalMax As Date
Dim MyRange As Range

    Set MyRange = Worksheets("Pivot Tables").PivotTables("pvttblDates").PivotFields("[tblDates].[Dates].[Dates]").DataRange
    LocalMax = Application.WorksheetFunction.Max(MyRange)
    MyReportDate = Format(LocalMax, "yyyy-mm-dd")
    Sheets("Pivot Tables").PivotTables("pvttblDates").PivotFields("[tblDates].[Dates].[Dates]").VisibleItemsList = Array("[tblDates].[Dates].&[" & MyReportDate & "T00:00:00]")
 

Watch MrExcel Video

Forum statistics

Threads
1,118,751
Messages
5,574,018
Members
412,563
Latest member
marianmalone2019
Top