VBA Help for PivotTable Filter Update

[nsfekeci]

Hi,

This should be a fairly easy problem to fix, but I'm trying to update the date filters in a series of pivots using a named range within excel. My code is below and works fine as is, but I'm trying to replace the hardcoded date parameter in the last line with StartDate, which takes a date from the "Starting_Month" named range. This will always be set to a valid date.

Private Sub UpdateDateRange()
'
' Update Date Range for Pivot Slicers and Tables
'
Dim StartDate As Date
Dim Pivot_sht As Variant


Set Pivot_sht = ThisWorkbook.Worksheets("Pivots_HCMovement")
StartDate = ThisWorkbook.Worksheets("Summary_HCMovement").Range("Starting_Month")


    Pivot_sht.PivotTables("Start_Summary").PivotFields( _
        "[Headcount Data].[Report Date].[Report Date]").VisibleItemsList = Array( _
        "[Headcount Data].[Report Date].&[2018-04-30T00:00:00]")

End Sub

When I replace [2018-04-30T00:00:00] with StartDate, it gives me this error:

"Run -time error '1004': The query did not run or the Data Model could not be accessed. Here's the error message we got: The 'StartDate' string cannot be converted to the date type."

What's the correct way to take the string as it appears in the "Starting_Month" named range and convert it into the Data Model date format so I can use excel to change the filters automatically.

Private Sub UpdateDateRange()
'
' Update Date Range for Pivot Slicers and Tables
'
Dim StartDate As Date
Dim Pivot_sht As Variant


Set Pivot_sht = ThisWorkbook.Worksheets("Pivots_HCMovement")
StartDate = ThisWorkbook.Worksheets("Summary_HCMovement").Range("Starting_Month")


    Pivot_sht.PivotTables("Start_Summary").PivotFields( _
        "[Headcount Data].[Report Date].[Report Date]").VisibleItemsList = Array( _
        "[Headcount Data].[Report Date].&StartDate")

End Sub

Thanks for the help,

Nick