Results 1 to 11 of 11
  1. #1
    3 Star Lounger
    Join Date
    Feb 2003
    Posts
    363
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Changing a Pivot Tbl with vba (2000)

    On a worksheet I have a Pivot Table with County names in the Left Col
    I turned on the recorder and used the County Name drop-down to check off one more county to show in the PT.

    I then turned off the recorder and manually UNchecked the same county in the drop down - returning to my
    original PT display.
    When I then tried to use the code (follows) I get an error msg:

    "Unable to set the Visible property of the PivotItem class"

    Is there a Reference that I need to set in the VBE, or what am I missing?



    <pre>Sub TestPivotTblChange()

    With ActiveSheet.PivotTables("PivotTable3").PivotFields ("County Name")
    .PivotItems("COLBERT").Visible = True
    End With
    End Sub</pre>


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

    Re: Changing a Pivot Tbl with vba (2000)

    Have you set the pivot table to AutoSort ascending or descending? You can hide a pivot item using VBA if AutoSort is on, but not show it again. If you set AutoSort to manual, you can make the pivot item visible. If you wish, you can set AutoSort to manual in code, then make the pivot item visible, and finally restore the old AutoSort setting.

  3. #3
    3 Star Lounger
    Join Date
    Feb 2003
    Posts
    363
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Changing a Pivot Tbl with vba (2000)

    Precisely correct!
    I was given this project by a customer who developed the PT first. I had no idea it was set on AutoSort.
    Your solution was perfect.
    thanks

  4. #4
    New Lounger
    Join Date
    Oct 2007
    Posts
    5
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Changing a Pivot Tbl with vba (2000)

    I tried setting the AutoSort to manual and still can't get this to work. Any other ideas?

  5. #5
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Changing a Pivot Tbl with vba (2000)

    Welcome to Woody's Lounge!

    Can you provide details of what you're trying to do?
    Perhaps you could attach a stripped down copy of your workbook (remove or alter sensitive information before posting it)

  6. #6
    New Lounger
    Join Date
    Oct 2007
    Posts
    5
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Changing a Pivot Tbl with vba (2000)

    The file is to large to send even stripped down. Here's the section of code that's giving me a problem.

    Sheets("TBD Report").Select
    ActiveSheet.Unprotect
    Range("C5").Select
    ActiveSheet.PivotTables("TBD Report").PivotFields("Start Quarter").AutoSort _
    xlManual, "Start Quarter"
    With ActiveSheet.PivotTables("TBD Report").PivotFields("Start Quarter")
    .PivotItems("1Q07").Visible = False
    .PivotItems("ThisQtr").Visible = True
    .PivotItems("Next1").Visible = True
    .PivotItems("Next2").Visible = True
    .PivotItems("Next3").Visible = True
    End With

  7. #7
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Changing a Pivot Tbl with vba (2000)

    Does it help if you move the line for the Visible property of the "1Q07" item down to below the lines for the other pivot items?

    If not, where does the error occur and what does the error message say?

  8. #8
    New Lounger
    Join Date
    Oct 2007
    Posts
    5
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Changing a Pivot Tbl with vba (2000)

    No, I've tried moving it to the end of the list and still get the same error. Error text is "Run-time error '1004'; Unable to set the Visible property of the PivotItem class". It doesn't seem to matter which item is listed first, it always errors out on the first item.

  9. #9
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Changing a Pivot Tbl with vba (2000)

    I'm afraid I have no way of knowing what causes this without seeing the workbook. Sorry.

  10. #10
    New Lounger
    Join Date
    Oct 2007
    Posts
    5
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Changing a Pivot Tbl with vba (2000)

    Here's the file. To cause the error click on "Run Macro" on the instruction sheet. I've removed all the sheets except the one that's creating the error.
    Attached Files Attached Files

  11. #11
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Changing a Pivot Tbl with vba (2000)

    Thanks. The problem is that none of the pivot items that you want to make visible (Next1, Next2 and Next3) exist. Excel cannot unhide items that don't exist. And since 1Q07 is currently the only visible item, you cannot hide it. If you replace Next1 etc. with existing pivot items, the code runs OK, but the only ones actually present are 1Q07, 2Q07 and 3Q07.

Posting Permissions

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