Selecting Pivot Items with VBA

[CPF2011]

I have a report that I run daily and then create Pivot Tables off of it. I know how to create the code to completely un-filter the entire pivot table in order to refresh it without errors. However subsequent to that I am trying to then re-filter it - select specific pivot items from that pivot table. The issue I am running into is that at times the specific items that I want to select are not there; though the next day they could be.

In other words I have 10 possible items I want to have show up amongst 50 others. However any one of those 10 might be missing on any given day (since there was nothing associated with that lable that day).

I know how to select items individually however how do I get past the error when the code can't find the item?

This is the code I am using.

Sub AddFilter()

With ActiveSheet.PivotTables(1).PivotFields("Status")
.PivotItems("RECC").Visible = False
.PivotItems("WETC").Visible = False
.PivotItems("(Blank)").Visible = False

End With

End Sub

If, for example, that day "RECC" is not there I get the error

"Unable to get the PivotItems property of the PivotField class"

I tried several ways to "on error go to next" and they all failed.

 

[dk]

Hi

You should be able to use On Error Resume Next e.g.

Sub AddFilter()

    With ActiveSheet.PivotTables(1).PivotFields("Status")
        On Error Resume Next
        .PivotItems("RECC").Visible = False
        .PivotItems("WETC").Visible = False
        .PivotItems("(Blank)").Visible = False
        On Error GoTo 0
    End With

End Sub

HTH
DK

 

[CPF2011]

That did it - THANKS!

 

[T_Holliday]

Just a purely academic comment/question from a self-taught coder, but I'm wondering if there's anything better or worse about the solution I would have used: iterate through each item on the PivotField and select case on it's name.

Sub AddFilter()
Dim pi As PivotItem
    With ActiveSheet.PivotTables(1).PivotFields("Status")
        For Each pi In .PivotItems
            Select Case pi.Name
                Case "RECC", "WETC", "(blank)"
                    pi.Visible = False
            End Select
        Next pi
    End With
End Sub

 

[dk]

Just a purely academic comment/question from a self-taught coder, but I'm wondering if there's anything better or worse about the solution I would have used: iterate through each item on the PivotField and select case on it's name.

Sub AddFilter()
Dim pi As PivotItem
    With ActiveSheet.PivotTables(1).PivotFields("Status")
        For Each pi In .PivotItems
            Select Case pi.Name
                Case "RECC", "WETC", "(blank)"
                    pi.Visible = False
            End Select
        Next pi
    End With
End Sub

There's certainly nothing worse about your code. The only potential issue I can think of is that if there were many pivot items in the pivot field then looping through every item would be slower than using the method I posted - however, the performance difference would be negligible unless we were talking about 1000s of pivot items or the macro was being executed many times. The On Error Resume next method is ok as long as it's used properly - I've seen some examples where someone's stuck it in at the start of a proc but it then hides all errors - even if the error means that the procedure cannot complete successfully. So in that respect your code is probably better

 

[tutincho]

If its only 1:
activesheets.PivotTables(1).PivotFields(1).CurrentPage = "XXX"