D
Dennis Cheung
I had the following code from one of the masters here.
Task: Select an item in PivotTable1, Excel select the same item in
PivotTable2. PivotTable2 is a copy of PivotTable1. Items are in a field
called "Shop".
Rerult: After I select an item in PivotTable1, it hided all items in
PivotTable2 except the item was selected in PivotTable1. It means the task
was completed. But in the next time I select another item in PivotTable2,
nothing was changed since everything had beed hided.
Help: I think I need to add somethng into the code to unhide all items in
PivotTable2 before it does the hiding.
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
Task: Select an item in PivotTable1, Excel select the same item in
PivotTable2. PivotTable2 is a copy of PivotTable1. Items are in a field
called "Shop".
Rerult: After I select an item in PivotTable1, it hided all items in
PivotTable2 except the item was selected in PivotTable1. It means the task
was completed. But in the next time I select another item in PivotTable2,
nothing was changed since everything had beed hided.
Help: I think I need to add somethng into the code to unhide all items in
PivotTable2 before it does the hiding.
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