Dependent Pivot Tables

Hi,

I'm new to this forum, so am hoping someone can help.

I have a problem which really has me stumped. I have a worksheet containing several pivot tables. I would like all the pivots to automatically update the items within the colum fields (columnfields.Pivotitems) when I update the one "masterpivot".

In other words if the column header is animals, and I only want to see dogs, and cats, that should automatically update all pivots with those choices.

Using VBA Code I have come up with a "sort-of" solution, but I am not happy with it. One problem is that in order to reset each pivot before re-populating the column items I tried to set pivotitems.visible = false. This doesn't work as you have to always have at least one item selected.

I hope I am making sense.

Here is my code (I know it's a mess, I'm ust a beginner)

Thanks in advance

Sub Dependent_Pivot()
'First step making pivot items visible based on another pivot table

Dim pt As PivotTable
Dim pf As PivotField
Dim pi As PivotItem
Dim strPI As String

Sheets("Sheet4").Select
Range("A4").Select ' the location in the master pivot of the first column item.
strPI = ActiveCell.Value
For Each pt In ActiveSheet.PivotTables
  If pt.Name <> "MasterPivot" Then
    Set pf = pt.PivotFields("Bus Org")
        'On Error Resume Next
        pf.PivotItems(strPI).Visible = True  'somehow causing an error each time
        With pf
         For Each pi In pf.PivotItems
          If pi.Value = strPI Then pi.Visible = True Else pi.Visible = False
        Next pi
        End With
            For n = 1 To 100 'loops through the master pivot and sets each item as visible in other pivots
                If ActiveCell.Offset(n, 0).Value = "Grand Total" Then 'last row in master pivot
                n = 100
                Else: strPI = ActiveCell.Offset(n, 0).Value
                    With pf
                    .PivotItems(strPI).Visible = True
                    End With
                End If
  
            Next n
    Else
    End If
    Next pt
       
    
 End Sub