Filter on pivot table values after pivotupdate event

[jaryszek]

Hi Guys,

i have pivot table like here:
Screenshot_1.png

and what i want is to add filter on values to exclude values <> 0.
Manually when i am setting this up it is working like a charm without any issues.

I recorded even macro to this via VBA but i am getting error:
Autofilter method of range class failed.

Private Sub Worksheet_PivotTableUpdate(ByVal Target As PivotTable)

Dim AddressPivot As Range
Dim strAddress As String

    ActiveSheet.Range("$A$1:$C$10").AutoFilter Field:=3, Criteria1:=Array("165" _
        , "22", "55", "88"), Operator:=xlFilterValues
        
    ActiveSheet.Range("$A$1:$C$10").AutoFilter Field:=3
 
End Sub

Code to this is like above.
The perfect solution would be to take address from target pivot table and use it to filter specific field.

Please help,
Jacek

[GlennUK]

Have you considered also putting the age in the Report Page filter area, multiple items option, and unticking 0?

[jaryszek]

Hi,

I didnt think about it.
It will be reaplying after refreshing table?

Best,
Jacek

[GlennUK]

Thinking about it further, can I ask whether you can have a new extra column in the data? A simple flag of a formula of =C2>0 type of thing ... giving either TRUE or FALSE, and use that in the Report Page filter ... it will be correct on refresh then.

[jaryszek]

Hmm it is not working after refreshing.

I have to still do this manually...

Jacek

[GlennUK]

Hmm it is not working after refreshing.

I have to still do this manually...

Jacek

What isn't? My suggestion of having an extra column, then putting it in the Report Page filter works fine ... see attached:

[jaryszek]

Ok but this can work using something like:

    ActiveSheet.PivotTables("PivotTable1").PivotFields("TotalServers").CurrentPage _
        = "(All)"
    With ActiveSheet.PivotTables("PivotTable1").PivotFields("TotalServers")
        .PivotItems("1").Visible = True
    End With
    ActiveSheet.PivotTables("PivotTable1").PivotFields("TotalServers"). _
        EnableMultiplePageItems = True

If i will create filter field in my pivot table.
How to add criteria to make this "<>0"?

Please help,
Jacek

[jaryszek]

Ok it is working.

Thank you. But with my specific client i can not add additional column.

Do you know maybe how to create code for filtering in VBA?

Jacek

[GlennUK]

Ah, OK. Well, can you see if this does what you want:

        On Error Resume Next
        For Each pt In ActiveSheet.PivotTables("PivotTable1").PivotFields("TotalServers").PivotItems
            pt.Visible = True
        Next
        On Error GoTo 0
        With ActiveSheet.PivotTables("PivotTable1").PivotFields("TotalServers")
            .PivotItems("0").Visible = False
        End With

[jaryszek]

Thank you!!!

Jacek