+ Reply to Thread
Results 1 to 5 of 5

Unable to set the visible items of the Pivot Table

  1. #1
    Valued Forum Contributor
    Join Date
    12-02-2009
    Location
    Austin, Tx
    MS-Off Ver
    Office 365 64-Bit
    Posts
    3,728

    Unable to set the visible items of the Pivot Table

    I have the code below, which I've been using for over a year to determine the items to show on my pivot table. The code is repeated 12 different times for 12 different Expense Detail files every morning. This morning, the code started displaying the "Unable" message at the line "PI.Visible = True" on one specific Expense Detail report. I can't see anything different with this expense detail report; it DOES have a "PROJECTED" column, and if I stop the code at the error and refresh the table it all appears just as it should, with "PROJECTED" hidden. Here's my tried and true code:
    Please Login or Register  to view this content.
    I tried a change to see if this would work better. Same problem.
    Please Login or Register  to view this content.
    Any help with troubleshooting this one is greatly appreciated.

  2. #2
    Registered User
    Join Date
    09-09-2011
    Location
    Bangalore, India
    MS-Off Ver
    Excel 2003 & 2007
    Posts
    19,164

    Re: Unable to set the visible items of the Pivot Table

    Is it a 1004 error?

    Did you browse thru this thread? http://www.excelforum.com/excel-prog...tem-class.html

    You had helped the OP here.
    If I have helped, Don't forget to add to my reputation (click on the star below the post)
    Don't forget to mark threads as "Solved" (Thread Tools->Mark thread as Solved)
    Use code tags when posting your VBA code: [code] Your code here [/code]

  3. #3
    Valued Forum Contributor
    Join Date
    12-02-2009
    Location
    Austin, Tx
    MS-Off Ver
    Office 365 64-Bit
    Posts
    3,728

    Re: Unable to set the visible items of the Pivot Table

    Arlu,
    Thanks for reminding me of past glories. I didn't try the "Compare Text" option (but should have!), but I did check to make sure the "PROJECTED" was the same in all instances, and it was. I still don't know why it caused a problem, but I fixed it by altering my code like this:
    Please Login or Register  to view this content.
    Since earlier in my code I call the pivot table to show all, all I really needed to do at this step was to turn off the "PROJECTED". I haven't closed the thread yet because I want to run it tomorrow morning to make sure it's working correctly.

  4. #4
    Forum Moderator Paul's Avatar
    Join Date
    02-05-2007
    Location
    Wisconsin
    MS-Off Ver
    2016/365
    Posts
    6,881

    Re: Unable to set the visible items of the Pivot Table

    Hi Jomili,

    Just an observation in the last bit of code you posted. Since you're already using 'With PT.PivotFields("MOS2")' you shouldn't have to repeat all that on the next line. (Or just leave out the With/EndWith and put it all in one line since you're not referencing it multiple times.)
    Please Login or Register  to view this content.

  5. #5
    Valued Forum Contributor
    Join Date
    12-02-2009
    Location
    Austin, Tx
    MS-Off Ver
    Office 365 64-Bit
    Posts
    3,728

    Re: Unable to set the visible items of the Pivot Table

    Thanks Paul; I had noticed that before, and thought I changed it, but sure enough I didn't. I appreciate the help.

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1