VBA to pull back a Name if value of the data is > 0 in the pivot table

Originally Posted by xladept

Hi Marty,

I got no compile error from this code - is there anything else??

Hi xladept,

The code does not run when the pivot table is updated by the slicer. Is there a way that I should be referencing it differently?


Also when i created the code as just a sub routine and manually ran it in the VBA editor I was able to pull back all of the names, when I just wanted to pull back the ones that have a 1 in the census count from my pivot table.

Hope this makes sense and thanks in advanced!

Originally Posted by alansidman

Your post does not comply with Rule 3 of our Forum RULES. Use code tags around code.

Posting code between [CODE]Please [url=https://www.excelforum.com/login.php]Login or Register [/url] to view this content.[/CODE] tags makes your code much easier to read and copy for testing, it also maintains VBA formatting.

Highlight your code and click the # icon at the top of your post window. More information about these and other tags can be found here



(I have added to the thread for you. Please read our rules and abide by them in the future.)

Thank you and I apologize for the inconvenience

Originally Posted by xladept

Maybe:

Please Login or Register  to view this content.

That worked! Now my code runs when the slicer is updated but I am receiving the following error:

Unable to get the pivotitems property of the pivotfield class

It is failing on the following line of code:

Please Login or Register  to view this content.

Here is a complete look of all my code with the one change:

Please Login or Register  to view this content.

Thanks for everyones help so far! you guys rock!

Originally Posted by xladept

Could it be overflow? There's no limiting code for these increments:

Please Login or Register  to view this content.

No because I put a break point in at the beginning of the For Each statement and it broke right on the next line of :

Please Login or Register  to view this content.

Originally Posted by xladept

Please Login or Register  to view this content.

???

I am trying to have the code look at the pivot table (screen shot is in the first post) where the am census cnt is >0 to pull back the corresponding am name that is associated with that respective count.

I am not sure the way I coded it was correct. I am only going off of what I found on line with how to reference pivot tables using vba online.


If there is a better way to do this please let me know. My VBA skills are very minimal.

Thanks in advanced!

I ended up figuring it out.

For those that are looking to do something similar here is my final code:

Please Login or Register  to view this content.

Thanks to all that responded to my post.