Having trouble with latest date in vba code for a pivot table

[MinerDuncan]

Having trouble with latest date code for pivot table vba code.
Trying to streamline our process of entering equipment hours and I started getting into vba since it seems like it's the way to go, I found some code that should do this but I can't get it working in my particular instance. Listed below is what I used but I get a "Run-Time eror '13': Type Mismatch" on this line "sFilterCrit = ThisWorkbook.Worksheets("Eq Pivot").Range("B1").Value" A solution would be greatly appreciated.
Thanks

Sub Filter_PivotField()
'Description: Filter a pivot table for a specific date or period
Dim sSheetName As String
Dim sPivotName As String
Dim sFieldName As String
Dim sFilterCrit As Double
Dim pt As PivotTable
Dim pf As PivotField
Dim pi As PivotItem

'Set the variables
sSheetName = "Pivot"
sPivotName = "PivotTable6"
sFieldName = "ExtractDate"
'sFilterCrit = "2/29/2024" --most recent date
sFilterCrit = ThisWorkbook.Worksheets("Eq Pivot").Range("B1").Value

Set pt = ThisWorkbook.Worksheets("Eq Pivot").PivotTables(sPivotName)
Set pf = pt.PivotFields("Shift Date")

pf.ClearAllFilters


For Each pi In pf.PivotItems
    If CDbl(DateValue(pi)) = sFilterCrit Then
        pi.Visible = True
    Else
        pi.Visible = False
    End If
Next pi
End Sub

 

[shina67]

The error you're encountering is because you're trying to assign the value of a cell, which is likely a date formatted as text, to a variable declared as a double. To fix this, you should declare the sFilterCrit variable as a Date type instead of a Double. Here's the corrected code:

Sub Filter_PivotField()
'Description: Filter a pivot table for a specific date or period
Dim sSheetName As String
Dim sPivotName As String
Dim sFieldName As String
Dim sFilterCrit As Date ' Change from Double to Date
Dim pt As PivotTable
Dim pf As PivotField
Dim pi As PivotItem

'Set the variables
sSheetName = "Pivot"
sPivotName = "PivotTable6"
sFieldName = "ExtractDate"
'sFilterCrit = "2/29/2024" --most recent date
sFilterCrit = ThisWorkbook.Worksheets("Eq Pivot").Range("B1").Value

Set pt = ThisWorkbook.Worksheets("Eq Pivot").PivotTables(sPivotName)
Set pf = pt.PivotFields("Shift Date")

pf.ClearAllFilters

For Each pi In pf.PivotItems
If DateValue(pi) = sFilterCrit Then ' Removed CDbl()
pi.Visible = True
Else
pi.Visible = False
End If
Next pi
End Sub

This change ensures that sFilterCrit is now a Date type variable, capable of handling date values without causing a type mismatch error.