Error '1004': Unable to set the Visible property of the PivotItem class RRS feed

  • Question

  • I used the below code to hide the Pivotitems:

    With pf .ClearAllFilters

    .AutoSort xlManual, .SourceName 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 the whole code is working fine and I'm facing error only the lines pi.Visible = True or pi.Visible = False

    I'm not sure where I've done wrong for the code not to work.

    Monday, July 25, 2016 4:37 PM


  • From this link = OLAP PTs - try using filling an array with all the items to show, leaving out the ones to hide and use visibleitemslist

    Monday, July 25, 2016 8:48 PM

All replies

  • You may need to loop through the items, like

    For i = 1 To pi.Count - 1

    pi.Item(i).Visible = False

    Next i

    Monday, July 25, 2016 5:37 PM
  • No.. That didn't worked either. Here is the code I've replaced:


        Dim pt As PivotTable
        Dim pf As PivotField
        Dim pi, firstPi As PivotItem

        Set pt = Sheets("Cancels").PivotTables("Cancels")
        Set nwSheet = Worksheets.Add
        Set pf = pt.PivotFields(6)
        rw = 0

    For i = 2 To pf.PivotItems.Count - 1 itemValue = pt.GetPivotData("[Measures].[Nr of Cancelled]", "[Characteristics].[Reason]", pf.PivotItems(i).Name).Value If itemValue > 2000 Then rw = rw + 1 nwSheet.Cells(rw, 1).Value = pvtitem.Name nwSheet.Cells(rw, 2).Value = pvtitem.Visible nwSheet.Cells(rw, 3).Value = itemValue Else pf.PivotItems(i).Visible = False End If Next i

    I'm getting the same error. Let me know if I did what you've suggested


    • Edited by Pramod_Duvvuri Monday, July 25, 2016 7:28 PM added declaration part of coding
    Monday, July 25, 2016 6:59 PM
  • Not sure what pf and pvtitem are - if you record a macro unchecking an item in your pivot table (manually making visible false), what code to you get?
    Monday, July 25, 2016 7:27 PM
  • Hi, Please refer to the updated comment.

    And, when I record a macro, this is the code I got:

        ActiveSheet.PivotTables("Cancels").PivotFields("[Characteristics].[Reason].[Reason]" _
            ).HiddenItemsList = Array("[Characteristics].[Reason].&[BILLING]")


    Monday, July 25, 2016 7:33 PM
  • Can you upload a sanitized version of your file to a sharing site and post the link here? And, what version of Excel are you using?
    Monday, July 25, 2016 7:40 PM
  • I'm using Excel 2007. And I'm sorry. I cannot upload anything from the machine I'm working. Its restricted.


    Monday, July 25, 2016 7:53 PM
  • I'm just curious to ask, is it possible to pass an array to hiddenitemslist? like:


    Dim reasons(30) As String
        ActiveSheet.PivotTables("Cancels").PivotFields("[Characteristics].[Reason].[Reason]" _
            ).HiddenItemsList = Array(reasons())

    I couldn't find any suitable way in internet. Can you suggest something?


    Monday, July 25, 2016 7:55 PM
  • From this link = OLAP PTs - try using filling an array with all the items to show, leaving out the ones to hide and use visibleitemslist

    Monday, July 25, 2016 8:48 PM
  • Thanks Bernie. That solved my problem. I've iterated through the pivot items and stored the needed Pivot Items in an array and passed that array to HiddenListItems.


    Tuesday, July 26, 2016 2:45 PM