Error "1004": Unable to set the Visible property of the PivotItem class

Question

I got the code below: The report filter cycle for changing visibility does not work , where the solution is marked as working. After changing according to my need, it looks like this:

With pt.PivotFields(6) .ClearAllFilters If .PivotItems.Count > 0 Then 'goofy but necessary Set firstPi = .PivotItems(1) For Each pi In .PivotItems If firstPi.Visible = False Then firstPi.Visible = True End If 'Don't loop through firstPi If pi.Value <> firstPi.Value Then itemValue = pt.GetPivotData("[Measures].[Nr of Cancelled]", "[Characteristics].[Reason]", pi.Name).Value rw = rw + 1 nwSheet.Cells(rw, 1).Value = pi.Name nwSheet.Cells(rw, 2).Value = pi.Visible If itemValue < 2000 Then If pi.Visible = True Then pi.Visible = False 'Error here End If Else MsgBox pi.Value If pi.Visible = False Then pi.Visible = True 'Error here End If End If End If Next 'Finally perform the check on the first pivot item If firstPi > 2000 Then firstPi.Visible = True Else firstPi.Visible = False End If End If End With 

I see that all the code is working fine, and I only encountered the error of the lines pi.Visible = True or pi.Visible = False

I'm not sure where I made a mistake so that the code doesn't work.

When I searched the internet for soltuion, I came across this link: https://support.microsoft.com/en-us/kb/114822 , where MS mentioned that Only adjacent elements in the summary field can be hidden. Does this mean that the elements in my table are not adjacent? Can someone help me? I'm lost here.

Answer 1

I did not find a solution for the error. But I found another way to achieve this. I used an array to store all the elements to hide and the elements that will be visible so that I can call either HiddenItemsList or VisibleItemsList:

  For Each pvtitem In pt.PivotFields(6).PivotItems On Error GoTo skipreason itemValue = pt.GetPivotData("[Measures].[Cancelled]", "[Characteristics].[Reason]", pvtitem.Name).Value If itemValue < 2000 Then hiddenReasons(hiddenCount) = pvtitem.Name hiddenCount = hiddenCount + 1 Else visibleReasons(visibleCount) = pvtitem.Name visibleCount = visibleCount + 1 End If Sheets("Cancels").PivotTables("Cancels").PivotFields( _ "[Characteristics].[Reason].[Reason]" _ ).VisibleItemsList = Array(visibleReasons())