Updating PivotTable filters based on other cell value

[dalew]

Hi there guy/girls!

Hope youre well.

Im using the following bit of code to try and get the filter of a pivottable to change based on the text displayed in a cell on the data sheet.

Please Login or Register  to view this content.

This works great! The problem ive got is if say "Joe Blogs" has made no customer contacts and his name is in Data!B1 the script will error. However is "Mary Smith" has made contacts and her name is in Data!B1 in the script will run, change the value and move on.

Can anyone think of a way to make the script just set the value as "(BLANK)" if the selected name is Data!B1 has made no contacts?

your help is greatly appreciated! My hair is falling out!

Cheers,
Dale

[MarvinP]

Hi Dale,

I've run into this problem a few times and have a suggestion.

PivotFields and PivotItems are indexed.. That means you can do code like

Please Login or Register  to view this content.

in the immediate window and it will return the name of the second PivotField
Also the

Please Login or Register  to view this content.

will return the number of PivotFields.

The big pictre is you loop through your Pivot Tables or Pivot Fields or Pivot Items from 1 to their Counts and do things.

This is the big picture and has worked for me but I'd need a sample file and exactly what youi are attempting to get a real answer.

[dalew]

Thanks for your post.

Im not quite sure i understand.

to clarify - my problem is the marco above works, its linked to a button and when the macro is ran there is no problem unless a the name in the range that the macro is looking at in this case "Data!B1"

To clarify - the marco above works with no problem unless the data in the range where the macro is looking - in this case "Data:B1" - does not exist in the filters for that pivot table.
For everyone else is works a treat!

The reason its important for it to skip over the error is there is a host of other commands that need to be ran after this including other pivot updates.

Thanks again for your post & i look forward to hearing back!

[MarvinP]

Hi Dale,
I guess I need a sample file with an example to help much more. I have filtered pivot items using code a few times but use the index number of the item to do it. I have not use the For Each loop and would need to see why it doesn't work for you. I also don't quite understand the a single cell filter name range and how this applies to the pivot items.

[dalew]

Hi Dale,
I guess I need a sample file with an example to help much more. I have filtered pivot items using code a few times but use the index number of the item to do it. I have not use the For Each loop and would need to see why it doesn't work for you. I also don't quite understand the a single cell filter name range and how this applies to the pivot items.

Thanks for your response.

Ive attached a file. I hope this helps! This is still wrecking my brain and ive had all night to think about it!

Cheers

[MarvinP]

Hi Dale,

I'm on my way out the door but here is some code I think you'll want to study

Please Login or Register  to view this content.

The idea is it goes throught all the pivotitems and makes the item show.

I was thinking you could use a similar loop to turn on all your pivot items.

If this doesn't help, reply back and I'll look at your code tomorrow.

[dalew]

Hi Dale,

I'm on my way out the door but here is some code I think you'll want to study

Please Login or Register  to view this content.

The idea is it goes throught all the pivotitems and makes the item show.

I was thinking you could use a similar loop to turn on all your pivot items.

If this doesn't help, reply back and I'll look at your code tomorrow.

Hi Marvin,

Thanks again!

Ive looked over this however i dont think that will work. From what i understand - and keep in mind im clearly a novice compared to you - that just selects all the data and skips?

I look forward to your thoughts on the file i uploaded. Youll see how i have got the macro and how it errors. I hope it will shine some light on the problem to you and clear all confusion.

Cheers,
Dale

[MarvinP]

Hi Dale,
You can't unselect all filter items but try this code. It is a work-a-round where if the name isn't found then I set the LInked Name1 filter to not show anyone.

Please Login or Register  to view this content.

[YoExceller]

Hello I found this thread...
I need a similar solution -
I would like the pivot table filter to be based the on a selected cell - automatically. Is that possible?

[Cutter]

Hello YoExceller. Please read the following. Thanks.

Your post does not comply with Rule 2 of our Forum RULES. Don't post a question in the thread of another member -- start your own thread. If you feel it's particularly relevant, provide a link to the other thread. It makes sense to have a new thread for your question because a thread with numerous replies can be off putting & difficult to pick out relevant replies.