Updating Pivot Table Date Field

[elevate_yourself]

Hi All,

Im trying to work on a VBA script for updating a pivot table date field based on a reference cell value and am running into some issues.

The code below is what im working with. Right now when I run this I get an "Application-defined or object-defined error" with the red text below.
Any help would be appreciated!

Sub RefreshPivot()
    
    Dim pt As PivotTable
    Dim ValDate As Date
    'This macro will Refresh ODS Pivots


    StartTime = Now()
    Application.ScreenUpdating = False
    'Create string for pivot field date selection
    dtVal_Date = Sheets("Sheet1").Range("ValDate").Value
    dtyear = Format(Year(dtVal_Date), "0000")
    dtMonth = Format(Month(dtVal_Date), "00")
    dtday = Format(Day(dtVal_Date), "00")
    dtDate = dtyear & "-" & dtMonth & "-" & dtday
    strDate_Filter = "[System Date].[Date Hierarchy].&[" & dtDate & "T00:00:00]"


    
    For Each pt In Sheets("Sheet1").PivotTables
        With pt.PivotFields("[System Date].[Date Hierarchy].[Date]")
[COLOR=#ff0000][FONT=arial black]            .CurrentPageName = strDate_Filter[/FONT][/COLOR]
        End With
    Next pt
 
End Sub

 

[elevate_yourself]

Managed to get this working. The issue was with how the pivot field was defined. Posting final code just for reference.

Sub RefreshPivot()
    
    Dim pt As PivotTable
    Dim ValDate As Date




    StartTime = Now()
    Application.ScreenUpdating = False
    'Create string for pivot field date selection
    dtVal_Date = Sheets("Sheet1").Range("ValDate").Value
    dtyear = Format(Year(dtVal_Date), "0000")
    dtMonth = Format(Month(dtVal_Date), "00")
    dtday = Format(Day(dtVal_Date), "00")
    dtDate = dtyear & "-" & dtMonth & "-" & dtday
    strDate_Filter = "[System Date].[Date Hierarchy].[Date].&[" & dtDate & "T00:00:00]"
    'strDate_Filter = "[System Date].[Date Hierarchy].&[" & dtDate & "]"
    
       
    For Each pt In Sheets("Sheet1").PivotTables
        With pt.PivotFields("[System Date].[Date Hierarchy].[Year]")
            .CurrentPageName = strDate_Filter
        End With
    Next pt
 
End Sub