Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Macro Help!!
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! -- Dennis Cheung |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Macro 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 Dennis Cheung wrote: 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! |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Macro Help!!
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? -- Dennis Cheung "Rowan" wrote: 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 Dennis Cheung wrote: 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! |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Macro Help!!
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? |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Macro Help!!
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? |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Macro Help!!
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? |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
Macro Help!!
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? |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
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? |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Macro recorded... tabs & file names changed, macro hangs | Excel Worksheet Functions | |||
Macro Help Needed - Excel 2007 - Print Macro with Auto Sort | Excel Worksheet Functions | |||
how to count/sum by function/macro to get the number of record to do copy/paste in macro | Excel Programming | |||
macro to delete entire rows when column A is blank ...a quick macro | Excel Programming | |||
Start Macro / Stop Macro / Restart Macro | Excel Programming |