Slicer update based on cell value

[crystalneedshelpplzthnx]

Hi,

I run a report 7 days a week and have a few slicers per page. I have the slicers linked, but I need to update two of the slicers everyday to the latest date and the latest week. I have tried several slicers after looking online:

Sub Filter_PivotField()


Dim sSheetName As String
Dim sPivotName As String
Dim sFieldName As String
Dim sFilterCrit As String
Dim pi As PivotItem


    sSheetName = "Sales Summary"
    sPivotName = "Slicer_Day_of_Date"


'I'm not sure if this name is correct, I'm not sure what the code needs:
    sFieldName = "Day of Date"


'I get subscript our of range error here:
sFilterCrit = ThisWorkbook.Worksheets("Chart Data").Range("p10").Value


With ThisWorkbook.Worksheets(sSheetName).PivotTables(sPivotName).PivotFields(sFieldName)


For Each pi In .PivotItems
If pi.Name <> sFilterCrit Then
pi.Visible = False

End If

Next pi


End With


End Sub

When I manually select slicers I get this:

Sub Macro1()
    With ActiveWorkbook.SlicerCaches("Slicer_Day_of_Week")
        .SlicerItems("3/3/2018").Selected = True
        .SlicerItems("2/24/2018").Selected = False
        .SlicerItems("2/17/2018").Selected = False
    End With
    With ActiveWorkbook.SlicerCaches("Slicer_Day_of_Date")
        .SlicerItems("3/1/2018").Selected = True
        .SlicerItems("2/28/2018").Selected = False
        .SlicerItems("2/27/2018").Selected = False
        .SlicerItems("2/26/2018").Selected = False
        .SlicerItems("2/25/2018").Selected = False
        .SlicerItems("2/24/2018").Selected = False
        .SlicerItems("2/23/2018").Selected = False
        .SlicerItems("2/22/2018").Selected = False
        .SlicerItems("2/21/2018").Selected = False
        .SlicerItems("2/20/2018").Selected = False
        .SlicerItems("2/19/2018").Selected = False
        .SlicerItems("2/18/2018").Selected = False
        .SlicerItems("2/17/2018").Selected = False
        .SlicerItems("2/16/2018").Selected = False
        .SlicerItems("2/15/2018").Selected = False
        .SlicerItems("2/14/2018").Selected = False
        .SlicerItems("2/13/2018").Selected = False
        .SlicerItems("2/12/2018").Selected = False
        .SlicerItems("2/11/2018").Selected = False
    End With
End Sub

I would prefer to loop through all available selections until the date in the cell is found. The cell is on another tab, but can be hidden on same tab is necessary.

Also, where would I store the code. Is it possible to save in my Personal workbooks module?

Thank you

 

[crystalneedshelpplzthnx]

I just found this and it works great to select all options in all the slicers. I'm sure it can be used to select 1 option on 1 slicer based on a cell value. What do you think?

Sub SlicerReset()
    
Dim sc As SlicerCache
Dim sl As Slicer
Dim i As Integer
For Each sc In ActiveWorkbook.SlicerCaches
    For Each sl In sc.Slicers
        For i = 1 To sc.SlicerItems.Count
            sc.SlicerItems(i).Selected = True
        Next i
    Next sl
Next sc


End Sub

 

[crystalneedshelpplzthnx]

I found one that WORKS!!! But I need to reference a cell value on another sheet.
Can anyone assist with incorporating


Tab "Chart Data" Cell "P10"

into

Sub itworks()


For Each slcCache In ActiveWorkbook.SlicerCaches
        slcCache.ClearManualFilter
Next


With ActiveWorkbook.SlicerCaches("Slicer_Day_of_Date")
    For Each oSlicerItem In .SlicerItems
        If oSlicerItem.Name = "2/12/2018" Then
            oSlicerItem.Selected = True
        Else
            oSlicerItem.Selected = False
        End If
    Next oSlicerItem
End With
End Sub

 

[crystalneedshelpplzthnx]

Found answer on another site for anyone who is interested:

To select the last Slicer item, try something like this

[COLOR=#333333][I]Sub test()
    Dim i As Long, n As Long
    
    With ActiveWorkbook.SlicerCaches("Slicer_Account")
        n = .SlicerItems.Count
        
        For i = 1 To n - 1
            .SlicerItems(i).Selected = False
        Next I
        
        .SlicerItems(n).Selected = True
    End With
End Sub[/I][/COLOR]

Modded for my purposes...

Sub Update_Slicer_Date()
    Dim i As Long, n As Long
    
    With ActiveWorkbook.SlicerCaches("Slicer_Day_of_Week")
        n = .SlicerItems.Count
        
        For i = 2 To n - 0
            .SlicerItems(i).Selected = False
        Next i
        
        .SlicerItems(n).Selected = False
    End With
    
        With ActiveWorkbook.SlicerCaches("Slicer_Day_of_Date")
        n = .SlicerItems.Count
        
        For i = 2 To n - 0
            .SlicerItems(i).Selected = False
        Next i
        
        .SlicerItems(n).Selected = False
    End With
End Sub