Linking Two Pivot Tables

Hi all,

Recently, I was making a dashboard and I had to create two Pivot Tables. My conditional formatting was set in such a manner, that I needed to have both the pivot tables updated @ same time, and the items too, to be shown or hid @ the same time in both the pivot table.

To achieve this, I first noted down the fields which I had to filter. I had three fields to filter and these items were Name, Date, and Month. So I had to make the following code :


Private Sub Worksheet_PivotTableUpdate(ByVal Target As PivotTable)
'ExpectedHours

If (Target.Name = "MainPivot") Then
      Macro1
End If

End Sub

Sub Macro1()
'

'
'On Error Resume Next
'
      Dim pivotSource As PivotTable
      Dim pivotTarget As PivotTable
      Dim sourceItem As PivotItem
      Dim sourceField As PivotField
      Dim targetItem As PivotItem
      Dim targetField As PivotField
      
      Set pivotSource = Sheet7.PivotTables("MainPivot")
      Set pivotTarget = Sheet7.PivotTables("ExpectedHours")
      
      
    Set targetField = pivotTarget.PivotFields("department")
   
    targetField.ClearAllFilters
    targetField.CurrentPage = Sheet7.Range("Department").Value
   
   
    Set sourceField = pivotSource.PivotFields("Name")
    Set targetField = pivotTarget.PivotFields("Name")
   
    For Each sourceItem In sourceField.PivotItems
            
            Set targetItem = GetPivotItemByName(targetField, sourceItem.Name)
            
            If (Not (targetItem Is Nothing)) Then
            
                  If (targetItem.Visible <> sourceItem.Visible) Then
                        targetItem.Visible = sourceItem.Visible
                  End If
            End If
    Next sourceItem
      
      
    Set sourceField = pivotSource.PivotFields("Date")
    Set targetField = pivotTarget.PivotFields("Date")
   
    For Each sourceItem In sourceField.PivotItems
            
            Set targetItem = GetPivotItemByName(targetField, sourceItem.Name)
            
            If (Not (targetItem Is Nothing)) Then
            
                  If (targetItem.Visible <> sourceItem.Visible) Then
                        targetItem.Visible = sourceItem.Visible
                  End If
            End If
    Next sourceItem
      
            
    Set sourceField = pivotSource.PivotFields("Month")
    Set targetField = pivotTarget.PivotFields("Month")
   
    For Each sourceItem In sourceField.PivotItems
            
            Set targetItem = GetPivotItemByName(targetField, sourceItem.Name)
            
            If (Not (targetItem Is Nothing)) Then
                  If (targetItem.Visible <> sourceItem.Visible) Then
                        targetItem.Visible = sourceItem.Visible
                  End If
                  
                  If (targetItem.ShowDetail <> sourceItem.ShowDetail) Then
                        targetItem.ShowDetail = sourceItem.ShowDetail
                  End If
            End If
    Next sourceItem
End Sub


Public Function GetPivotItemByName(field As PivotField, item As String) As PivotItem
      On Error Resume Next
      Dim pvItem As PivotItem
      
      Set pvItem = Nothing
      
      Set pvItem = field.PivotItems(item)
      
      Set GetPivotItemByName = pvItem
End Function

Hope this helps :)

Thanks,
Vikas