使用 VBA 将一个 excel 选项卡中的多个数据透视表更新到昨天的日期

问：

单击 excel 中的按钮时，我正在尝试更改数据透视表中的日期字段，以便在我的选项卡中的所有数据透视表中更改它，以便它仅选择昨天的日期。

出现错误的行是：
pf.PivotFilters.Add Type:=xlCaptionEquals, Value1:=dateStr

Sub UpdatePivotDateFilter()
    Dim ws As Worksheet
    Dim pt As PivotTable
    Dim pf As PivotField
    Dim currentDate As Date
    Dim dateStr As String
    
    ' Set the worksheet to the "Pivots -1" tab
    Set ws = ThisWorkbook.Sheets("Pivots -1")
    
    ' Calculate yesterday's date
    currentDate = Date - 1
    ' Convert to a string in the format "yyyy-mm-dd"
    dateStr = Format(currentDate, "yyyy-mm-dd")
    
    ' Loop through all pivot tables in the worksheet
    For Each pt In ws.PivotTables
        ' Check if the pivot table has a "Date" filter field
        If pt.PageFields.Count > 0 Then
            ' Assuming the "Date" filter field is the first field in the PageFields
            Set pf = pt.PageFields(1)
            
            ' Clear all filters
            pf.ClearAllFilters
            
            ' Set the filter to yesterday's date
            *pf.PivotFilters.Add Type:=xlCaptionEquals, Value1:=dateStr*
        End If
    Next pt
End Sub