Wednesday, April 14, 2010

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