Pivot Tables-How to ignore?

[jomili]

I run a series of macros which open workbooks, manipulate the pivot tables therein, and bring the data into another workbook, wherin the data is massaged and then a pivot table is created. With my code below I turn off what I don't want to see. However, if one of the items I list as "Visible = False" doesn't appear in the list of PivotItems, the code fails with an error code 1004, "Unable to get the PivotItems property of the PivotFields class". How do I configure it so that if the pivot item doesn't exist just ignore it?

Please Login or Register  to view this content.

[davegugg]

I think I'd do it with an error catcher:

Please Login or Register  to view this content.

This will ignore the specific error of no pivot items in the pivot fields class, but will display all other errors before exiting the sub. One other way to do it would probably be to loop through all the pivot items and make sure that each one you want to turn off is present before turning it off. It'd probably go something like this:

Please Login or Register  to view this content.

You'd have to play around with the PivotItems collection to get the exact syntax.

[jomili]

Dave,

I opted for the error catcher, assuming it would be faster. However, it doesn't appear to be working correctly. When I run it I get the msgbox (the "else" part of your code) saying

Please Login or Register  to view this content.

I don't understand why that would still be appearing.

[davegugg]

You need to make sure the error description text EXACTLY matches the error description you get when running without the error catcher.

One way to do this, run the code without the error catcher and when you get an error, type "print err.description" into the immediate window and press enter. Take the text it returns and paste it back into your code after "If err.Description = ". Does that do it?

[jomili]

Well, I didn't see any visible difference in the error code, but pasting it back in DID make a difference; when I reenabled the error checker and ran it again I got an error code with only a "0" in it. Now what in the world does that mean?

[davegugg]

You need to exit the sub before your code gets down to the error catcher. For example, if your code is this:

Please Login or Register  to view this content.

You need to add an Exit Sub where I commented so the error catcher code only runs if there is an error.

[jomili]

A-ha! That "exit sub" did the trick. Now we're cookin'!

Thanks so much for walking me through this. Another trick I can add to my tools. Thanks loads!

[davegugg]

You're welcome John. It is great to work with someone who responds promptly, uses the right amount of detail when describing issues, and is always courteous.

[jomili]

Thanks for commenting on my better qualities. I think they're all nicely balanced by the fact that I'm constantly in need of help so am always pestering the folks on this forum. Thanks to people like you I'm actually learning a bit of this, a little bit at a time.