Unable to set the visible items of the Pivot Table

[jomili]

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.

[arlu1201]

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.

[jomili]

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.

[Paul]

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.

[jomili]

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