Unhide items in PivotTable
 

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

--
Dennis Cheung

Unhide items in PivotTable
 

Hi Dennis

As per your original request the code below adjusts pivottable2 to show
the same items as pivottable1. It will not allow you to change
pivottable2 independantly of pivottable1. If that is not what you want
to do then we would need to revist this starting with you explaining in
a bit more detail how you would like this to work, e.g. what do you want
to have happen when selecting items from pivottable1 and what do you
want to have happen when selecting items from pivottable2.

Regards
Rowan

Dennis Cheung wrote:
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