For each pivot item in pivot table - for data model

**dancing-shadow** · 10-23-2017, 09:29 AM

I have a sheet with some 'normal' pivots, as well as a couple of data models (for the distinct count feature).

I have some code which loops through the pivot items and selects the one that matches my string. This works fine for the normal pivots, but I can't get it to work for the data model ones - it throws up an error of 'Object doesn't support this property or method'. Any help/suggestions welcome!

Please Login or Register  to view this content.

**xlnitwit** · 10-23-2017, 09:33 AM

Hi,

You forgot the .Pivotitems on the end of that line.

**dancing-shadow** · 10-23-2017, 10:19 AM

Ah, thank you!

This certainly clears the error, but now when I step through the code it jumps straight from the line that had the error, to the next line of code immediately after Next, as though it can't actually find any pivot items. Not sure if this has anything to do with the fact that in the data model filter you have to click the + to expand the list first before you can manually select/deselect items?

**xlnitwit** · 10-23-2017, 11:09 AM

If you expand the list manually, does the code then loop through the items?

**dancing-shadow** · 10-23-2017, 11:35 AM

It's not possible to expand the list whilst running the code - as soon as you select something other than the filter selection box it deactivates it and it reverts back to the condensed list.

**xlnitwit** · 10-23-2017, 11:38 AM

Can you post a sample workbook and clarify which version of Excel you are actually using- it's clearly not 2003 or 2007.

**dancing-shadow** · 10-23-2017, 02:45 PM

Apologies - it's 2010. I don't have a sample workbook - would have to knock one up due to the financial data I'm using. Bear with

Edit: there you go. This file/pivots and code is doing exactly what my 'real' file is doing. Runs through with no errors, but won't actually filter on the data model pivot.

As an addition to this, I have an if statement now in my code that either has the 'Import' as one singular field ("A", "B", "C"), or turns it into an array (Three different arrays depending on criteria). If I'm filtering a pivot on a single item, no problem, but it's not that simple with an array. I've been looking at the code Jerry has suggested here, but not sure how to implement it for my own use.