Excel VBA: clear items in pivot table

2
votes

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")

2
vbaexcelfilterpivot-table

2 Answers

2
votes

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)

1
votes

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