Pivot items filtered by named range

[cjwilkinson82]

I've been looking all over the internet yesterday to find some vba code which is able to have my pivotitems in my pivottable filtered by a named range containing multiple items.

Furthermore, the code should be applied whenever the contents of the Named range are changed.

Pivottable is called ptProductAnalysis (resides on "NPI_GroupDB" worksheet)
Pivotfield is called "Group"
Named range is "NPI_GroupOffset"

Basically, whatever is in NPI_GroupOffset (NamedRange) should be displayed in ptProductAnalysis (Pivottable) in the Group (Pivot field)

[DonkeyOte]

A sample file would generally help in these instances but I wonder if perhaps something along the lines of the below would work for you ?

Please Login or Register  to view this content.

The above should be placed in the Sheet object that contains the named range NPI_GroupOffset - to ensure this is placed correctly right click on the tab name in XL select View Code and paste above into resulting window - thereafter ensure Macros are enabled. As you alter the content of the named range you should find the PT Field is filtered according to the content of the named range.

[cjwilkinson82]

That works perfectly, but apparently I think a line needs to be added somewhere so the macro starts of by setting the pivot items to show all, otherwise you get errors in certain cases.

What code do I need to add for this?

[DonkeyOte]

hmm... in "theory" the code should be cycling all the items in the Field (visible or not) and setting visible status of said item to True/False depending on whether or not the item is listed in the named range - that said it was coded off the cuff so I have not tested it!

Could you post a sample please ?

[cjwilkinson82]

I found the solution,

I just added the following before you have it match the named range with the pivot items:

Please Login or Register  to view this content.

It does give an error when the contet of the named range is empty, but normally, in the report I'm building, it is not possible to have nothing selected. I thought your code would take care of that, but unfortunately it doesn't:

Please Login or Register  to view this content.

[DonkeyOte]

If you want a tailored solution you will need to provide a representative sample I'm afraid - you don't for ex. specify how the Named Range is defined so it's not clear if this is dynamic or not. There's not really a great deal we can do with limited info / nothing against which to test the concept etc...