Unable to get the PivotFields property of the PivotTable class

[mssbass]

Code is below - I'm receiving this error and double-checked the name of my worksheet, pivottable and field name that is in the filter - any other ideas?

Sub Filter_Multiple_Pivots()
'Call the Filter Pivot macro on multiple pivots

Dim sFilter1 As String
Dim sFilter2 As String

'Set the filter criteria
sFilter1 = ThisWorkbook.Worksheets("Detail by Sup").Range("A27").Value
sFilter2 = ThisWorkbook.Worksheets("Detail by Sup").Range("B27").Value

'Call the filter pivot macro to filter both pivots
Call Filter_PivotField_Args("Detail by Sup", "PivotTable10", "Day", sFilter1)
Call Filter_PivotField_Args("Detail by Agent", "PivotTable1", "Day", sFilter2)

End Sub
Sub Filter_PivotField_Args( _
sSheetName As String, _
sPivotName As String, _
sFieldName As String, _
sFilterCrit As String)
'Filter a pivot table or slicer for a specific date or period

Dim pi As PivotItem

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

 

[Alex Blakenburg]

Assuming your day field is a date try adding this before your " 'Loop through pivot items " line

        ' Convert Date to US Format
        If IsDate(sFilterCrit) Then
            sFilterCrit = Month(sFilterCrit) & "/" & Day(sFilterCrit) & "/" & Year(sFilterCrit)
        End If

 

[mssbass]

Still received the same error - the pivot table is from the data model - when recording a macro, the code looks like this:

ActiveSheet.PivotTables("PivotTable10").PivotFields("[T_Summary].[Day].[Day]"). _
ClearAllFilters
ActiveSheet.PivotTables("PivotTable10").PivotFields("[T_Summary].[Day].[Day]"). _
CurrentPage = "[T_Summary].[Day].&[2021-10-26T00:00:00]"

When I run this same code after recording it, I get the same error - Unable to set the CurrentPage property of the PivotField class. Wonder if it just won't work on a pivot that has been added to the data model?

 

[mssbass]

This code is getting closer - I'm able to clear the filter for my first pivot table that is built from the data model but cannot re-set the filter to the last date. For the 2nd pivot table, I'd rather set the date rather than looping through all the dates which takes time and recalculates each time.

Sub Filter_Multiple_Pivots()
'Call the Filter Pivot macro on multiple pivots

Dim sFilter1 As Date
Dim sFilter2 As Date

'Set the filter criteria
sFilter1 = Date - 1
sFilter2 = Date - 1

'Call the filter pivot macro to filter both pivots
Call Filter_PivotField_Args("Detail by Sup", "PivotTable10", "[T_Summary].[ReportDt].[ReportDt]", sFilter1)
Call Filter_PivotField_Args("Detail by Agent", "PivotTable1", "Day", sFilter2)

End Sub
Sub Filter_PivotField_Args( _
sSheetName As String, _
sPivotName As String, _
sFieldName As String, _
sFilterCrit As Date)
'Filter a pivot table or slicer for a specific date or period

Dim pi As PivotItem
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

 

[Alex Blakenburg]

Still received the same error - the pivot table is from the data model - when recording a macro, the code looks like this:

ActiveSheet.PivotTables("PivotTable10").PivotFields("[T_Summary].[Day].[Day]"). _
ClearAllFilters
ActiveSheet.PivotTables("PivotTable10").PivotFields("[T_Summary].[Day].[Day]"). _
CurrentPage = "[T_Summary].[Day].&[2021-10-26T00:00:00]"

When I run this same code after recording it, I get the same error - Unable to set the CurrentPage property of the PivotField class. Wonder if it just won't work on a pivot that has been added to the data model?

Try changing the last line from CurrentPage to CurrentPageName.

    ActiveSheet.PivotTables("PivotTable10").PivotFields("[T_Summary].[Day].[Day]"). _
        ClearAllFilters
    ActiveSheet.PivotTables("PivotTable10").PivotFields("[T_Summary].[Day].[Day]"). _
        CurrentPageName = "[T_Summary].[Day].&[2021-10-26T00:00:00]"