Macro Help!!
You're welcome!
Dennis Cheung wrote:
Dear Rowan,
It works perfect. Thanks a lot.
Dennis
"Rowan" wrote in message
...
Hi Dennis
The code for page fields is quite different from what I provided before.
Try
Private Sub Worksheet_Change(ByVal Target As Range)
On Error GoTo ErrorHandler
Application.EnableEvents = False
Me.PivotTables("PivotTable2").PivotFields("Shop"). CurrentPage = _
Me.PivotTables("PivotTable1").PivotFields("Shop"). CurrentPage.Value
ErrorHandler:
Application.EnableEvents = True
End Sub
Hope this helps
Rowan
Dennis Cheung wrote:
Dear Rowan,
Thanks for your support.
The field "Shop" is at the top of the page, I think it called page area,
not a column ior a row in the PIvot Table, no check box. PivotTable2 is
in same design as PivotTable1.
I need Excel performs followong after a shop is selected. A shop is
selected means click on a shop in the pull down menu in PivotTable1,there
is no check box.
The PivotTable2 will change to the same shop after a shop was selected in
PivotTable2. The field "Shop" in PivotTable2 is located the page area as
same as PivotTable1.
I think the code you post almost did the task. It hided all un-necessary
item in PivotTable2 after an item was selected in PivotTable1. But the
next time selecting an item in PivotTable1, it finds nothing to hide
since everything had been hided already. I think to add something into
the code to make all items visible before hiding would work.
Dennis
"Rowan" wrote in message
. ..
Hi Dennis
Can we clear up what you mean by Selecting an Item. Are clicking on the
dropdown on arrow next to header "Shop" and then adding a check mark next
to one or more shops, or are you just clicking on a shop in the list?
Regards
Rowan
Dennis Cheung wrote:
Thanks Rowan! It works only in the 1st time. I select an item in PVT1
and the PVT2 change to the same item as PVT1. And I notice that all
items in "Shop" were hided except the item I select in PVT1.
In the 2nd time I select an item in PVT1, the item in "Shop" was not
changed in PVT2.
Can you help please?
|