Excel VBA: clear items in pivot table
Solution 1
you need to set CurrentPage
(and you shouldn't need to clear it first).
Using what is shown in your code, I would have something like:
Sheet8.PivotTables("capbylp").PivotFields("Product Family"). _
PivotFields("MyPivotField").CurrentPage = Range("B33").Value
(broken into 2 lines for readability)
Solution 2
This is slightly related; I wanted to clear multiple-selections whenever the user makes them. Apparently, setting the VisibleItemsList can do that.
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Dim xPF As PivotField
Dim nms As Variant
nms = Array("Calculation", _
"Rate Type", _
"xx Hierarchy")
Set xPT = Application.ActiveSheet.PivotTables(1)
For Each xPF In xPT.PageFields
For Each nm In nms
If xPF.Name Like "*" & nm & "*" Then
If UBound(xPF.VisibleItemsList) > 1 Then
xPF.VisibleItemsList = Array("")
End If
End If
Next nm
Next xPF
End Sub
Ben
Updated on June 04, 2022Comments
-
Ben almost 2 years
I am new to VBA... I am trying to write a macro that will clear all the selections within a pivot table filter named "Product Family" and select only the item whose name is contained in cell "B33". I am referencing the pivot table in one sheet "sheet8" and trying to change a graph on "Dashboard".
Here is the code...
Sub thisisalsotemp() ' ' thisisalsotemp Macro ' ' Sheets("Dashboard").Select ActiveSheet.ChartObjects("Chart 1").Activate Sheet8.PivotTables("capbylp").PivotFields("Product Family").PivotFields.ClearAllFilters With Sheet8.PivotTables("capbylp").PivotFields("Product Family") .PivotItems(Range("B33")).Visible = True End With End Sub
The error is in the following line: Sheet8.PivotTables("capbylp").PivotFields("Product Family").PivotFields.ClearAllFilters
The error message is: Object doesn't support this property or method
@SeanCheshire: Thanks for the help. I feel this is much closer to what I want. However, I couldnt get it to work. I played around with it a little bit and am closer. here is what i have...
Sub thisisalsotemp2() Sheets("Dashboard").Select Sheet8.PivotTables("capbylp").PivotFields("Product Family") = Range("B33") End Sub
Error 1004 reads: unable to set the pivotfields property of the pivottable class
in the line: Sheet8.PivotTables("capbylp").PivotFields("Product Family") = Range("B33")