Results 1 to 3 of 3
  1. #1
    Lounger
    Join Date
    Mar 2003
    Posts
    31
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Hide-Unhide Pivot Items (2000/xp)

    I have a pivot table and I want to a user to be able to click on a button to hide certain pivot items from a pivot field and then use another button to reset and display all pivot items from a pivot field. I have used the following code to hide all pivot items with the exception of pivot item "Won". This code works fine.

    Set pvttable = wshSummary.PivotTables("PivotTable2")
    For Each pvtitem In pvttable.PivotFields("Sales Stage").PivotItems
    pvtitem.Visible = False
    wshSummary.PivotTables("PivotTable2").PivotFields( "Sales Stage").PivotItems("Won").Visible = True
    Next

    However when I modify the code to display all Pivot items using the following I get a run time error "Unable to set the visible property of the pivot item class". The code is working for some items and then stops.

    wshSummary.Activate
    Set pvttable = wshSummary.PivotTables("PivotTable1")
    For Each pvtitem In pvttable.PivotFields("Sales Stage").PivotItems
    pvtitem.Visible = True
    Next
    End Sub

    Any ideas?

  2. Subscribe to our Windows Secrets Newsletter - It's Free!

    Get our unique weekly Newsletter with tips and techniques, how to's and critical updates on Windows 7, Windows 8, Windows XP, Firefox, Internet Explorer, Google, etc. Join our 480,000 subscribers!

    Excel 2013: The Missing Manual

    + Get this BONUS — free!

    Get the most of Excel! Learn about new features, basics of creating a new spreadsheet and using the infamous Ribbon in the first chapter of Excel 2013: The Missing Manual - Subscribe and download Chapter 1 for free!

  3. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 16 Times in 16 Posts

    Re: Hide-Unhide Pivot Items (2000/xp)

    It seems that you cannot set pivot items to visible in VBA if the items are auto-sorted. Does this newsgroup post help?

  4. #3
    Lounger
    Join Date
    Mar 2003
    Posts
    31
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Hide-Unhide Pivot Items (2000/xp)

    Thank you Hans. This does work.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •