Excel Slicers

[Maebus]

I have a workbook with multiple sheets. Each sheet has an independent Date slicer so that the other sheets aren't affected when someone uses a slicer on a different sheet. I’m trying to find a way to have each Date slicer to automatically be set to the most recent date that’s available on the slicers (if the most recent date is 9/9 on the slicer although it’s 9/13) upon opening the workbook. I’ve tried several variations of VBA coding but nothing is working. Any help or suggestions would be appreciated.

 

[johnny51981]

Are you trying to VBA the Slicers or Date on the Sheets themselves?

Since Slicers are just remote AutoFilters, if you run a VBA of filtering for the Max of your Date column on each sheet, the Slicer should show the most recent date highlighted.

 

[Maebus]

Are you trying to VBA the Slicers or Date on the Sheets themselves?

Since Slicers are just remote AutoFilters, if you run a VBA of filtering for the Max of your Date column on each sheet, the Slicer should show the most recent date highlighted.

So the slicers are connected to pivot tables that are located on a separate sheet than where the slicer is. I just need a VBA code that will set the slicers to the most recent date. What would the VBA code for that look like?

 

[Maebus]

Here is a screenshot of the code. The code is clearing slicer filters but not selecting the most recent date which is todays date minus one.

 

[Maebus]

Here is the code I’m working with. The only part not working is where it selects the previous date (date minus one).

Private Sub Workbook_Open()

Dim ws As Worksheet

Dim cacheIndex As Integer

Dim itemIndex As Integer

Dim mostRecentDate As Date

Dim mostRecentSlicerItem As slicerItem

Dim slicerCache As slicerCache

Dim slicerItem As slicerItem



‘ Activate the "Home" sheet

ThisWorkbook Sheets ("Home") .Activate



‘ Store the most recent date available (today's date minus 1 day)

mostRecentDate = Date - 1



‘ Loop through each slicer cache in the workbook

For Each slicerCache In ThisWorkbook.SlicerCaches

‘ Clear all manual filters in the slicer cache

slicerCache.ClearManualFilter



‘ Loop through each slicer item in the current slicer cache

For Each slicerItem In slicerCache. SlicerItems
If IsDate(slicerItem.Name) And Format(slicerItem.Name, "[$-en-US]d-mmmm;@") = Format(slicerItem.Name, "[$-en-US]d-mmmm;@") Then
If DateValue(slicerItem. Name) › mostRecentDate Then
mostRecentDate = DateValue(slicerItem.Name)
Set mostRecentSlicerItem = slicerItem
End If
End If

Next slicerItem

‘ Determine the most recent date in the slicer cache For Each slicerItem In slicerCache.SlicerItems
If IsDate(slicerItem.Name) And Format (slicerItem.Name, "[$-en-US]d-mmmm;@*) = Format(slicerItem. Name, "[$-en-US ]d-mmmm;@") Then
If DateValue (slicerItem. Name) › mostRecentDate Then
mostRecentDate = DateValue (slicerItem. Name)
Set mostRecentSlicerItem = slicerItem
End If
End If

Next slicerItem

‘ Select the most recent date in the slicer cache

If Not mostRecentSlicerItem Is Nothing Then

For Each slicerItem In slicerCache.SlicerItems

If slicerItem.Name = mostRecentSlicerItem. Name Then

slicerItem. Selected - True

Else

slicerItem.Selected = False

End If

Next slicerItem

End If

Next slicerCache

End Sub