Dear VBA expert,
I need your help, i wanna my pivot automatically select the recent date. this code i got from the other VBA group, this code was successful when running VBA code. but i got some pop up notification as unable set visible property pivot item class. the error line occurs to code "pi.Visible = False" as follows.
Sub FilterBlank()
Dim sSheetName As String
Dim sPivotName As String
Dim sFieldName As String
Dim sFilterCrit As String
Dim pi As PivotItem
'Set the variables
sSheetName = "PT.Drill_Install"
sPivotName = "PivotTable7"
sFieldName = "Date"
sFilterCrit = Format(Date, "m/d/yyyy")
sFilterCrit = ThisWorkbook.Worksheets(sSheetName).Range("AY1").Value
With ThisWorkbook.Worksheets(sSheetName).PivotTables(sPivotName).PivotFields(sFieldName)
'Clear all filter of the pivotfield
.ClearAllFilters
'Loop through pivot items of the pivot field
'Hide or filter out items that do not match the criteria
For Each pi In .PivotItems
If pi.Name <> sFilterCrit Then
pi.Visible = False
End If
Next pi
End With
End Sub
I need your help, i wanna my pivot automatically select the recent date. this code i got from the other VBA group, this code was successful when running VBA code. but i got some pop up notification as unable set visible property pivot item class. the error line occurs to code "pi.Visible = False" as follows.
Sub FilterBlank()
Dim sSheetName As String
Dim sPivotName As String
Dim sFieldName As String
Dim sFilterCrit As String
Dim pi As PivotItem
'Set the variables
sSheetName = "PT.Drill_Install"
sPivotName = "PivotTable7"
sFieldName = "Date"
sFilterCrit = Format(Date, "m/d/yyyy")
sFilterCrit = ThisWorkbook.Worksheets(sSheetName).Range("AY1").Value
With ThisWorkbook.Worksheets(sSheetName).PivotTables(sPivotName).PivotFields(sFieldName)
'Clear all filter of the pivotfield
.ClearAllFilters
'Loop through pivot items of the pivot field
'Hide or filter out items that do not match the criteria
For Each pi In .PivotItems
If pi.Name <> sFilterCrit Then
pi.Visible = False
End If
Next pi
End With
End Sub