I am automating a process that look at dates in a pivot table and make visible only the dates in the range. the code look for the last day that the program was run and make the range between that date and yesterday. The problem I am having is that if one of the dates in the range is missing from the dataset, the script stop and give me a "Unable to set the visible property of the PivotItem class" for the missing date. This is what I have so far
Sub test2()
'
' test2 Macro
'
Application.DisplayAlerts = False
Dim ws As Worksheet
Dim pt As PivotTable
Dim pf As PivotField
Dim pi As PivotItem
Dim sh As Worksheet
Dim Dt1 As Date
Dim lastDt As Date
Set sh = Sheets("Notes")
Set ws = Sheets("Auth Vol Sum")
Set pt = ws.PivotTables("PivotTable3")
Set pf = pt.PivotFields("Liab_dt")
Dt1 = Date - 1
lastDt = sh.Range("A9").Value
With pf
.ClearAllFilters
For Each pi In .PivotItems
If pi.Value = "(blank)" Then
pi.Visible = False
Else
If pi.Value = "" Then
Else
If pi.Value >= lastDt And pi.Value <= Dt1 Then
pi.Visible = True
Else
pi.Visible = False
End If
End If
End If
Next
End With
End Sub
Any ideas will be most appreciated.
Sub test2()
'
' test2 Macro
'
Application.DisplayAlerts = False
Dim ws As Worksheet
Dim pt As PivotTable
Dim pf As PivotField
Dim pi As PivotItem
Dim sh As Worksheet
Dim Dt1 As Date
Dim lastDt As Date
Set sh = Sheets("Notes")
Set ws = Sheets("Auth Vol Sum")
Set pt = ws.PivotTables("PivotTable3")
Set pf = pt.PivotFields("Liab_dt")
Dt1 = Date - 1
lastDt = sh.Range("A9").Value
With pf
.ClearAllFilters
For Each pi In .PivotItems
If pi.Value = "(blank)" Then
pi.Visible = False
Else
If pi.Value = "" Then
Else
If pi.Value >= lastDt And pi.Value <= Dt1 Then
pi.Visible = True
Else
pi.Visible = False
End If
End If
End If
Next
End With
End Sub
Any ideas will be most appreciated.