CODEWITHSUNDEEP
vbaexcel
AmadeusNing
AmadeusNing

Reputation: 107

VBA PivotTable Filter when existing

Hi I'm working on VBA in Excel to get my filter worked in the Pivot Table. I put a field in filter section and want to only tick "Account Invoice" and "Manual Match". Here's my code:

With wsPvtTbl.PivotTables("PivotTable1").PivotFields("Type")
        .PivotItems("Approve").Visible = False
        .PivotItems("Error").Visible = False
        .PivotItems("Interface Validation").Visible = False
        .PivotItems("Invoice Validation").Visible = False
        .PivotItems("Account Invoice").Visible = True
        .PivotItems("Manual Match").Visible = False
    End With

The problem comes up because sometimes the source data doesn't contain any "Error" item therefore there will be a bug when goes to ".PivotItems("Error").Visible = False"

Is there a way to fix it? many thanks.

Upvotes: 1

Views: 1799

Answers (1)

A.S.H
A.S.H

Reputation: 29332

Will this work?

....
On Error Resume Next
.PivotItems("Error").Visible = False
On Error Goto 0
....

On Error Resume Next will tolerate the non-existence of the Pivot Items "Error". So it is hidden if it exists. Afterwards, On Error Goto 0 will re-establish normal error handling so that further errors in the code are not skipped.

Alternatively, you can put those two statements at the beginning and the end of the code snippet, so that any non-existing pivot item wont break the code.

Upvotes: 3

Related Questions