Pivot Chart - Page Field Filtering

One of the few flaws I have found using pivot tables is that when selecting from the the page field it does not filter the other page fields to match your current selection.
It does in the actual table, so why not the page fields?

For example we have a pivot table like the following
|Country| (ALL)
|City| (ALL)

|Count of People|
|YEAR| ________ |Gender|
_______________Male__________Female
2008__________1000 _________500000
2009__________9999999_______1


If I select England from the country, when I go to select a City I do not want to be able to select only the cities in England and not every city in the world.

The Attached File shows you how to deal with this Problem.

Please feel free to copy or modify my solution and add it to your own Blogs
all I ask is you give me a little mention as it has taken me a while to figure it out.

Thanks

Chappo, you might be interested in the below thread from June (see post # 4) which will automate this process for you.

http://www.excelforum.com/excel-2007...rill-down.html

Wow, great minds really do think alike.
I think I like your method better though. especially with the EnableEvents part.

Thanks

Chappo