Results 1 to 15 of 15
  1. #1
    4 Star Lounger
    Join Date
    Jan 2003
    Location
    Central Florida, USA
    Posts
    505
    Thanks
    5
    Thanked 0 Times in 0 Posts

    Pivot Table Select Data (2003)

    I used the a recorded macro to create the following code.

    With PT.PivotFields("City")
    .PivotItems("Atlanta").Visible = False
    .PivotItems("Boston").Visible = False
    .PivotItems("Chicago").Visible = False
    .PivotItems("Dallas").Visible = False
    .PivotItems("Houston").Visible = False
    End With

    Is there a way to tell Excel to show a single item (city) instead of implicitly saying what NOT to show? In the above example, all works well until a new data set is brought in and "Houston" is no longer in the data set and/or a new city comes in. If Houston drops off, BANG! error If a new city comes in, BLAP! I get two cities in the pivot table instead of the original "Baltimore".

    Does this make sense?

    Thanks in advance for your ideas.

    Ken

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

    Re: Pivot Table Select Data (2003)

    You can loop through the pivot items and make only one item visible - see for example <post:=269,871>post 269,871</post:> (you'll have to modify it to suit your purposes).

  3. #3
    4 Star Lounger
    Join Date
    Jan 2003
    Location
    Central Florida, USA
    Posts
    505
    Thanks
    5
    Thanked 0 Times in 0 Posts

    Re: Pivot Table Select Data (2003)

    Hans,

    Thanks. I tried the following variation of your referenced post with poor results.
    With PT.PivotFields("City")
    For i = 1 To .PivotItems.count
    strAction = .PivotItems(i).Name
    If .PivotItems(i).Name <> "Baltimore" Then
    .PivotItems(i).Visible = False
    End If
    Next i
    End With

    I get the following message:
    "Unable to set the Visible property of the PivotItem class"

    Any ideas what I did wrong?

    thanks!

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

    Re: Pivot Table Select Data (2003)

    Does this work?

    With PT.PivotFields("City")
    .PivotItems("Baltimore").Visible = True
    For i = 1 To .PivotItems.Count
    If .PivotItems(i).Name <> "Baltimore" Then
    .PivotItems(i).Visible = False
    End If
    Next i
    End With

  5. #5
    4 Star Lounger
    Join Date
    Jan 2003
    Location
    Central Florida, USA
    Posts
    505
    Thanks
    5
    Thanked 0 Times in 0 Posts

    Re: Pivot Table Select Data (2003)

    Hans,
    It gets the same error, but on a different line. It errors on:
    .PivotItems("Baltimore").Visible = True

    Not sure how to proceed.

    Ken

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

    Re: Pivot Table Select Data (2003)

    Can you post (a stripped down copy of) the workbook?

  7. #7
    4 Star Lounger
    Join Date
    Jan 2003
    Location
    Central Florida, USA
    Posts
    505
    Thanks
    5
    Thanked 0 Times in 0 Posts

    Re: Pivot Table Select Data (2003)

    Hans,

    I will do that. It will take a while, so it may be much later on before I can get it posted. The VBA code is in Access, not Excel. Do you need that code as well?

    Thanks,

    Ken

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

    Re: Pivot Table Select Data (2003)

    You don't need to post a large workbook, just a small sample one.

    And I'll try to test with the code you posted first.

  9. #9
    Uranium Lounger
    Join Date
    Jan 2001
    Location
    South Carolina, USA
    Posts
    7,295
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Pivot Table Select Data (2003)

    A real stab in the dark here. Does this work?

    <code>
    With PT.PivotFields("City")
    If .PivotItems("Baltimore").Visible <> True Then
    .PivotItems("Baltimore").Visible = True
    End If
    For i = 1 To .PivotItems.Count
    If .PivotItems(i).Name <> "Baltimore" Then
    .PivotItems(i).Visible = False
    End If
    Next i
    End With
    </code>
    Legare Coleman

  10. #10
    4 Star Lounger
    Join Date
    Jan 2003
    Location
    Central Florida, USA
    Posts
    505
    Thanks
    5
    Thanked 0 Times in 0 Posts

    Re: Pivot Table Select Data (2003)

    Hans,

    Here is a stripped down version for your review.

    Thanks,
    Ken
    Attached Files Attached Files

  11. #11
    4 Star Lounger
    Join Date
    Jan 2003
    Location
    Central Florida, USA
    Posts
    505
    Thanks
    5
    Thanked 0 Times in 0 Posts

    Re: Pivot Table Select Data (2003)

    Legare,

    That didn't seem to help either, though I think I see your rational (Make at least one visible?)

    Thanks for your idea.

    Ken

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

    Re: Pivot Table Select Data (2003)

    Both my and Legare's versions of the macro work correctly in the workbook you attached, so what exactly is the problem?

  13. #13
    4 Star Lounger
    Join Date
    Jan 2003
    Location
    Central Florida, USA
    Posts
    505
    Thanks
    5
    Thanked 0 Times in 0 Posts

    Re: Pivot Table Select Data (2003)

    Hans,

    I just tried running both solutions in Excel and they worked! My challenge is I get the error when trying to run it from Access. Even though the following runs fine from Access:
    With PT.PivotFields("City")
    .PivotItems("Atlanta").Visible = False
    .PivotItems("Boston").Visible = False
    .PivotItems("Chicago").Visible = False
    .PivotItems("Dallas").Visible = False
    .PivotItems("Houston").Visible = False
    End With

    I can't get these two will not run without erroring. Have I left out an "xlapp" or something?

    Thanks to both of you for solutions. They do work in Excel.

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

    Re: Pivot Table Select Data (2003)

    I've just now tested the code from Access, and it runs without problems.

    I've seen only a small part of your code, so I can't tell you where it fails. I'd check carefully for unqualified use of Excel objects - you should know the routine by now. For example, the following line in your Excel macro

    With ActiveSheet.PivotTables("PivotTable2").PivotFields ("City")

    is perfectly OK when used within Excel, but if you run the code from Access it will cause problems because ActiveSheet hasn't been prefixed with the appropriate variable.

  15. #15
    4 Star Lounger
    Join Date
    Jan 2003
    Location
    Central Florida, USA
    Posts
    505
    Thanks
    5
    Thanked 0 Times in 0 Posts

    Re: Pivot Table Select Data (2003)

    Hans,

    I appreciate all your help. I will go back through the entire code and recheck. It is probably something careless. I'll let you know what I find.

    THANKS!

    Ken

Posting Permissions

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