Select item of a dropdown list (data validation) and refreshes values of all pivot tables

**siroco79** · 02-07-2014, 07:23 AM

Hi gurus,

I have a dropdown list, made with data validation, and I would like that all my pivot tables get automatically updated with the selection of the item.

Currently, I need to hit the refresh button to get them refreshed with the selection. Is there any wayt to automate this?

Many thanks!!!

**chinraj** · 02-07-2014, 07:37 AM

Try this,

Please Login or Register  to view this content.

**Fotis1991** · 02-07-2014, 07:45 AM

chinraj

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

**siroco79** · 02-07-2014, 07:52 AM

Thanks!

However my pivots are all accross my worksheet. Is it possible to apply this to the entire worksheet?

**chinraj** · 02-07-2014, 07:54 AM

Regret the inconvenience caused. I’ll ensure this henceforth. thanks

**chinraj** · 02-07-2014, 07:56 AM

yes. this applies to whole sheets in the workbook.

**Fotis1991** · 02-07-2014, 07:58 AM

Originally Posted by chinraj

Regret the inconvenience caused. I’ll ensure this henceforth. thanks

You have to add code tags in your code. Pls do it!

**siroco79** · 02-07-2014, 08:06 AM

Mmmmm I have just used it and unfortunately there must be something that I do wrongly but my pivot tables do not get updated (refreshed) when the selection is made.

I have put the code in workbook/view code/

I still need to hit the refresh button if I want to see my pivot tables and pivot charts updated.

**siroco79** · 02-07-2014, 11:14 AM

So, is there anyone who could help me with this??

Thanks!

**siroco79** · 02-12-2014, 04:42 AM

Hi there,

I need help with this, I don't know why the code above does not work refresh my pivot tables???

**Decar** · 02-12-2014, 04:53 AM

put some dummy data in your excel file and upload it. This way its very easy to look whats wrong. Gonna have a look later

**siroco79** · 02-13-2014, 05:15 AM

Hi there,

Attached an example with the suggested macro.

Follow this to reproduce the issue.

1. Go to the 'Charts' tab and check how the chart looks.
2. Go to 'Data Validation' tab and choose a country. (do not refresh!)
3. Go back to 'Charts' and you will see that the pivot chart did not update. The source of this pivot chart is on the "PIVOTSCHARTS" tab, which does not update either.

Many thanks for any help!

**Izandol** · 02-13-2014, 05:26 AM

Right-click Data validation tab, select 'View code' then you may add this code:

Please Login or Register  to view this content.

**siroco79** · 02-13-2014, 10:12 AM

Hi,

I have tried and it seems not to work with the example??

Thanks!

**siroco79** · 02-13-2014, 11:19 AM

Hi,

I have tried and it seems not to work with the example??

Thanks!

**Izandol** · 02-13-2014, 11:23 AM

It works here but I do not use Excel 2007. Does anything happen at all?

**siroco79** · 02-14-2014, 08:42 AM

No, nothing happens....? I am using 2010, any clue??

**Izandol** · 02-14-2014, 08:53 AM

I think you have put code in wrong place then - it must be in module for Data validation sheet. Also for 2010 you must replace Add2 with Add:

Please Login or Register  to view this content.

**siroco79** · 02-14-2014, 10:06 AM

I works with the example, even when not replacing Add2 (which I do not where to replace it, I do not see it in the code...?)
However, when using the code above with my file and replacing names and cells (instead of b2, c10). I get the following error on this specific line.
**************
Run-time error "438":
Object doesn't support this property or method.
***************

The name of my tab is Pivot master data and replace in the code with "Replace master data".... Also, I am pasting the code in the sheet where I have the validation..

What am I doing wrong?

Thanks a lot!

**Izandol** · 02-14-2014, 10:49 AM

What is actual code you are using?

**siroco79** · 02-14-2014, 11:04 AM

Please Login or Register  to view this content.

**Izandol** · 02-14-2014, 11:05 AM

This is not correct:

Please Login or Register  to view this content.

It must be:

Please Login or Register  to view this content.

**siroco79** · 02-14-2014, 11:25 AM

THANKS A LOT!!!!!

I should have noticed that before...What a change, it works now, of course!!!! Perfect! Loving it!

**chinraj** · 03-06-2014, 05:35 AM

Originally Posted by siroco79

Hi,

I have tried and it seems not to work with the example??

Thanks!

I have modified some codes and attached here. This will get auto-refresh at every five seconds. You can change the timer setting in Module1.

This works fine.

-chinraj-

Select item of a dropdown list (data validation) and refreshes values of all pivot tables

Re: Select item of a dropdown list (data validation) and refreshes values of all pivot tab

Re: Select item of a dropdown list (data validation) and refreshes values of all pivot tab

Re: Select item of a dropdown list (data validation) and refreshes values of all pivot tab

Re: Select item of a dropdown list (data validation) and refreshes values of all pivot tab

Re: Select item of a dropdown list (data validation) and refreshes values of all pivot tab

Re: Select item of a dropdown list (data validation) and refreshes values of all pivot tab

Re: Select item of a dropdown list (data validation) and refreshes values of all pivot tab

Re: Select item of a dropdown list (data validation) and refreshes values of all pivot tab

Re: Select item of a dropdown list (data validation) and refreshes values of all pivot tab

Re: Select item of a dropdown list (data validation) and refreshes values of all pivot tab

Re: Select item of a dropdown list (data validation) and refreshes values of all pivot tab

Re: Select item of a dropdown list (data validation) and refreshes values of all pivot tab

Re: Select item of a dropdown list (data validation) and refreshes values of all pivot tab

Re: Select item of a dropdown list (data validation) and refreshes values of all pivot tab

Re: Select item of a dropdown list (data validation) and refreshes values of all pivot tab

Re: Select item of a dropdown list (data validation) and refreshes values of all pivot tab

Re: Select item of a dropdown list (data validation) and refreshes values of all pivot tab

Re: Select item of a dropdown list (data validation) and refreshes values of all pivot tab

Re: Select item of a dropdown list (data validation) and refreshes values of all pivot tab

Re: Select item of a dropdown list (data validation) and refreshes values of all pivot tab

Re: Select item of a dropdown list (data validation) and refreshes values of all pivot tab

Re: Select item of a dropdown list (data validation) and refreshes values of all pivot tab

Re: Select item of a dropdown list (data validation) and refreshes values of all pivot tab

Thread Information

Users Browsing this Thread

Similar Threads

[SOLVED] Pivot tables- putting more than two field list item into the data

[SOLVED] Pivot Tables, can I use an external list to select data items?

Tags for this Thread

Bookmarks

Bookmarks

Posting Permissions