I am relatively new to VBA. I can’t find a code to exclude items in a filter on a pivot table filter. I am currently trying this but I get always get an error with the filter. For this one I get an error 1004 “Unable to get the Pivotfields property of the pivottable class”. Ideally I would also like to add a function that first selects all items in the filter then excludes multiple items.
Sub ExcludeItemsFromFilter()
Dim pt As PivotTable
Dim pf As PivotField
Dim pi As PivotItem
'Set the pivot table object
Set pt = ActiveSheet.PivotTables("PivotTable3")
'Set the pivot field object
Set pf = pt.PivotFields("Headquarter") **ERROR ON THIS LINE**
'Loop through all the pivot items in the pivot field object
For Each pi In pf.PivotItems
If pi.Name = "Applied Materials, Austin (58460)" Or pi.Name = "Applied Materials, Inc., Dallas (33741)" Then
pi.Visible = False 'Exclude the item from the filter
End If
Next pi
End Sub
I have also tried this and I get a error 438 “Object doesn’t support this property or method”.
Sub ExcludeItemsFromBICubeFilter()
Dim objCube As Object
Dim objFilter As Object
Dim strFilterName As String
Dim strItemName As String
'Set the filter name and item names to exclude
strFilterName = "Headquarter"
strItemName = "Applied Materials, Austin (58460)"
'Set the cube object
Set objCube = ActiveSheet.PivotTables("PivotTable3")
'Set the filter object
Set objFilter = objCube.Filters(strFilterName) **ERROR ON THIS LINE**
'Exclude the specified item from the filter
objFilter.Members(strItemName).Exclude = True
End Sub
I tried the above 2 codes and was trying to create a VBA code to exclude items in filter on a pivot table filter. I got errors
Andrew Levin is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
Check out our Code of Conduct.
2
I get the same 1004 error when it gets to the pivot field. I did successfully run your sample though. Pivot table screenshot
Andrew Levin is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
Check out our Code of Conduct.
Since you are setting pt to ActiveSheet.PivotTables("PivotTable3") make sure you are on the sheet with PivotTable3 when you run your macro, otherwise you will get error 1004.
I created a clean workbook with a very simple data table (below), inserted a Pivot Table (renamed to PivotTable3 to match your code), and ran your first code as-is (except for adding an apostrophe in your line with the error to separate the comment). Everything worked as expected – if the above tip doesn’t work, can you try the same and see if it’s a problem with your code or the workbook?
Data table:
Result:
Code:
Sub ExcludeItemsFromFilter()
Dim ws as Worksheet
Dim pt As PivotTable
Dim pf As PivotField
Dim pi As PivotItem
'Set worksheet object
Set ws = Sheets("your_sheet_with_pivotttable3") 'replace with your sheet name
'Set the pivot table object
Set pt = ws.PivotTables("PivotTable3")
'Set the pivot field object
Set pf = pt.PivotFields("Headquarter") ' **ERROR ON THIS LINE**
'Loop through all the pivot items in the pivot field object
For Each pi In pf.PivotItems
If pi.Name = "Applied Materials, Austin (58460)" Or pi.Name = "Applied Materials, Inc., Dallas (33741)" Then
pi.Visible = False 'Exclude the item from the filter
End If
Next pi
End Sub
2