I'm trying to filter items from a pivot table.
Public Sub Test()
Dim pi As PivotItem
Dim pt As PivotTable
Dim pf As PivotField
Set pt = Worksheets("Collections").PivotTables("Collections")
Set pf = pt.PivotFields(1)
pt.ClearAllFilters
For Each pi In pf.PivotItems
Debug.Print pi.Name
If pi.Caption = "00087" Then
pi.Visible = True
Else
pi.Visible = False
End If
Next pi
End Sub
If figured it'd be fairly straight forward, however, I get a
Runtime error '1004' Unable to set the visible property of PivotItem class
From what I understand, I can't set every one to false. But, I did a debug.print and all the visible properties are true to begin with, so I can't see why setting a .Visible property to false would be an issue.
If this is an OLAP PivotTable, then you can't iterate through the PivotItems collection. Instead, fire up the macro recorder, manually filter on the item of interest, then inspect the code that is produced.
If it's a PageField, you'll see something like this:
ActiveSheet.PivotTables("Pt1").PivotFields("[Table1].[test].[test]").ClearAllFilters
ActiveSheet.PivotTables("Pt1").PivotFields("[Table1].[test].[test]"). _
CurrentPageName = "[Table1].[test].&[2]"
If it's a Row or Column field, you'll see something like this:
ActiveSheet.PivotTables("Pt1").PivotFields("[Table1].[test].[test]"). _
VisibleItemsList = Array("[Table1].[test].&[3]")
As you can see, you don't need to iterate in either case, and the code that gets spat out should be enough for you to work out what to do.
If it's NOT an OLAP PivotTable, then you must be inadvertently setting them all to false...possibly because the .caption has been changed and so doesn't match the .name, or possibly because the "00087" item doesn't exist in your data. So what's happening is that it's not finding a PivotItem with the caption of "00087" in the PivotItems, and when you try to set the last one to hidden, there's no visible items left.
But regardless, iterating through PivotItems is horribly inefficient, and there's a much better way to approach this given you just want to filter on one item: Make the field of interest a PageField (i.e. put it in the Filters part of the PivotTable) and then set .EnableMultipleItems to FALSE and then simply set .CurrentPage = "00087"
To get the exact syntax, fire up the macro recorder, filter on the one item, and look at the code the macro recorder spits out.
If you don't want to change the layout of your PivotTable (i.e. can't make the field a PageField because you want it to remain in the ROWS or COLUMNS area) then see my answer at https://stackoverflow.com/a/39604425/2507160
For future reference, note that if you ever iterate over PivotItems, set the PivotTable's .ManualUpdate to TRUE while you make your changes, and set it back to FALSE afterwards to avoid the PivotTable refreshing after each and every change.
For more on efficiently programming PivotTables, check out my blog post at http://dailydoseofexcel.com/archives/2013/11/14/filtering-pivots-based-on-external-ranges/