Macro Help!!

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

dear masters,

This is the 2nd time I post this subject but I got no reply. Can anyone
kindly let me know if my task is possible. Or I will think to do it the other
way.

There are two pivot tables in one sheet. they are from the same source in an
Access data base. Actually the 2nd one is a copy of the first one.
In the page area, both PVTs had a field "Shop". I need a Marco to do the
follwoing.
When I select a shop in PVT1, the 2nd PVT will select the same shop
automatically.
Is it possible to perform the above tasks? Please help!
 
Assuming your pivot tables are called PivotTable1 and PivotTable2 then
you could use this worksheet change event.

Private Sub Worksheet_Change(ByVal Target As Range)
Dim itm As PivotItem
Dim shop As PivotField

On Error GoTo ErrorHandler
Application.EnableEvents = False

Set shop = Me.PivotTables("PivotTable1"). _
PivotFields("Shop")
For Each itm In Me.PivotTables("PivotTable2"). _
PivotFields("Shop").PivotItems
itm.Visible = shop.PivotItems(itm.Caption).Visible
Next itm
Set shop = Nothing
Set itm = Nothing
ErrorHandler:
Application.EnableEvents = True

End Sub

This is worksheet event code. Right click the sheet tab, select View
Code and paste the code in there.

Hope this helps
Rowan
 
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?
 
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
 
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
 
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
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Macro to control 2 pivot tables 1
Make 2 Pivot Tables in same items 1
Pivot Table 1
Getting correct labels in Excel pivot 2
Pivot caches? 2
Newbie problem with Worksheet_Change 4
run a marco automatically 5
Code doesn't work from in Office 2003 2

Back
Top