I’m trying to filter a pivot table with vba. “Unable to get the PivotFields property of the PivotTable class”
The following code works as expected..
<code>Sub test4()
Dim FilterField As PivotField
Dim filter(3)
filter(1) = "[F_PMS_ReserveMaster].[RISK_ZIP].&[29502]"
filter(2) = "[F_PMS_ReserveMaster].[RISK_ZIP].&[74133]"
filter(3) = "[F_PMS_ReserveMaster].[RISK_ZIP].&[80111]"
Set FilterField = ThisWorkbook.Sheets("Test").PivotTables("RMZIPS").PivotFields("[F_PMS_ReserveMaster].[RISK_ZIP].[RISK_ZIP]")
FilterField.ClearAllFilters
ThisWorkbook.Sheets("Test").PivotTables("RMZIPS").RefreshTable
ThisWorkbook.Sheets("Test").PivotTables("RMZIPS").PivotFields("[F_PMS_ReserveMaster].[RISK_ZIP].[RISK_ZIP]").VisibleItemsList = filter
End Sub
</code>
<code>Sub test4()
Dim FilterField As PivotField
Dim filter(3)
filter(1) = "[F_PMS_ReserveMaster].[RISK_ZIP].&[29502]"
filter(2) = "[F_PMS_ReserveMaster].[RISK_ZIP].&[74133]"
filter(3) = "[F_PMS_ReserveMaster].[RISK_ZIP].&[80111]"
Set FilterField = ThisWorkbook.Sheets("Test").PivotTables("RMZIPS").PivotFields("[F_PMS_ReserveMaster].[RISK_ZIP].[RISK_ZIP]")
FilterField.ClearAllFilters
ThisWorkbook.Sheets("Test").PivotTables("RMZIPS").RefreshTable
ThisWorkbook.Sheets("Test").PivotTables("RMZIPS").PivotFields("[F_PMS_ReserveMaster].[RISK_ZIP].[RISK_ZIP]").VisibleItemsList = filter
End Sub
</code>
Sub test4()
Dim FilterField As PivotField
Dim filter(3)
filter(1) = "[F_PMS_ReserveMaster].[RISK_ZIP].&[29502]"
filter(2) = "[F_PMS_ReserveMaster].[RISK_ZIP].&[74133]"
filter(3) = "[F_PMS_ReserveMaster].[RISK_ZIP].&[80111]"
Set FilterField = ThisWorkbook.Sheets("Test").PivotTables("RMZIPS").PivotFields("[F_PMS_ReserveMaster].[RISK_ZIP].[RISK_ZIP]")
FilterField.ClearAllFilters
ThisWorkbook.Sheets("Test").PivotTables("RMZIPS").RefreshTable
ThisWorkbook.Sheets("Test").PivotTables("RMZIPS").PivotFields("[F_PMS_ReserveMaster].[RISK_ZIP].[RISK_ZIP]").VisibleItemsList = filter
End Sub
But when I add in building the array, I get the error “Unable to get the PivotFields property of the PivotTable class”.
<code>Sub test()
Dim rw As Long, rge As Range, i As Integer, str As String, arr As Variant
Dim FilterField As PivotField
Dim filter As Variant, state_abbr As String, filter2 As Variant
state_abbr = ThisWorkbook.Sheets("Controls").Range("Selected_State_Abbr").Text
ReDim filter(0)
rw = ThisWorkbook.Sheets("Test").Range("B1048576").End(xlUp).Row
str = "A2:b" & rw
Set rge = ThisWorkbook.Sheets("Test").Range(str)
arr = rge
For i = 1 To UBound(arr, 1)
If arr(i, 2) = state_abbr Then
ReDim Preserve filter(UBound(filter, 1) + 1)
str = "[F_PMS_ReserveMaster].[RISK_ZIP].&[" & arr(i, 1) & "]"
filter(UBound(filter, 1)) = str
End If
Next i
' getting rid of empty filter(0)
ReDim filter2(1 To UBound(filter, 1))
For i = 1 To UBound(filter2, 1)
filter2(i) = filter(i)
Next i
Set FilterField = ThisWorkbook.Sheets("Test").PivotTables("RMZIPS").PivotFields("[F_PMS_ReserveMaster].[RISK_ZIP].[RISK_ZIP]")
FilterField.ClearAllFilters
ThisWorkbook.Sheets("Test").PivotTables("RMZIPS").RefreshTable
'unable to get the pivot tables property of the pivot tables class?
ThisWorkbook.Sheets("Test").PivotTables("RMZIPS").PivotFields(FilterField).VisibleItemsList = filter2
</code>
<code>Sub test()
Dim rw As Long, rge As Range, i As Integer, str As String, arr As Variant
Dim FilterField As PivotField
Dim filter As Variant, state_abbr As String, filter2 As Variant
state_abbr = ThisWorkbook.Sheets("Controls").Range("Selected_State_Abbr").Text
ReDim filter(0)
rw = ThisWorkbook.Sheets("Test").Range("B1048576").End(xlUp).Row
str = "A2:b" & rw
Set rge = ThisWorkbook.Sheets("Test").Range(str)
arr = rge
For i = 1 To UBound(arr, 1)
If arr(i, 2) = state_abbr Then
ReDim Preserve filter(UBound(filter, 1) + 1)
str = "[F_PMS_ReserveMaster].[RISK_ZIP].&[" & arr(i, 1) & "]"
filter(UBound(filter, 1)) = str
End If
Next i
' getting rid of empty filter(0)
ReDim filter2(1 To UBound(filter, 1))
For i = 1 To UBound(filter2, 1)
filter2(i) = filter(i)
Next i
Set FilterField = ThisWorkbook.Sheets("Test").PivotTables("RMZIPS").PivotFields("[F_PMS_ReserveMaster].[RISK_ZIP].[RISK_ZIP]")
FilterField.ClearAllFilters
ThisWorkbook.Sheets("Test").PivotTables("RMZIPS").RefreshTable
'unable to get the pivot tables property of the pivot tables class?
ThisWorkbook.Sheets("Test").PivotTables("RMZIPS").PivotFields(FilterField).VisibleItemsList = filter2
</code>
Sub test()
Dim rw As Long, rge As Range, i As Integer, str As String, arr As Variant
Dim FilterField As PivotField
Dim filter As Variant, state_abbr As String, filter2 As Variant
state_abbr = ThisWorkbook.Sheets("Controls").Range("Selected_State_Abbr").Text
ReDim filter(0)
rw = ThisWorkbook.Sheets("Test").Range("B1048576").End(xlUp).Row
str = "A2:b" & rw
Set rge = ThisWorkbook.Sheets("Test").Range(str)
arr = rge
For i = 1 To UBound(arr, 1)
If arr(i, 2) = state_abbr Then
ReDim Preserve filter(UBound(filter, 1) + 1)
str = "[F_PMS_ReserveMaster].[RISK_ZIP].&[" & arr(i, 1) & "]"
filter(UBound(filter, 1)) = str
End If
Next i
' getting rid of empty filter(0)
ReDim filter2(1 To UBound(filter, 1))
For i = 1 To UBound(filter2, 1)
filter2(i) = filter(i)
Next i
Set FilterField = ThisWorkbook.Sheets("Test").PivotTables("RMZIPS").PivotFields("[F_PMS_ReserveMaster].[RISK_ZIP].[RISK_ZIP]")
FilterField.ClearAllFilters
ThisWorkbook.Sheets("Test").PivotTables("RMZIPS").RefreshTable
'unable to get the pivot tables property of the pivot tables class?
ThisWorkbook.Sheets("Test").PivotTables("RMZIPS").PivotFields(FilterField).VisibleItemsList = filter2
Any ideas?
New contributor
anna is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
Check out our Code of Conduct.