How to synchronize two or more pivot tables using VBA

One often comes across situations when one wants to synchronize two or more pivot tables. The problem arises when two or more pivot tables have the same underlying data set but show different views to the user. In such cases, combining both of them without losing data separation is not really an option.

I wrote this small piece of VBA code a few months back that can help synchronize pivots across a worksheet.

Private Sub Worksheet_Change(ByVal target As Range)
Dim a(10, 2) As Integer
Dim fieldadd(10, 2) As String

fieldadd(1, 1) = “$B$2” ‘Keep on adding the address of fields that will change here
fieldadd(1, 2) = “Name” ‘Keep on adding the name of field here

Application.ScreenUpdating = False

If (target.Address) = fieldadd(1, 1) Then
For Each pt In ActiveSheet.PivotTables

For Each pt1 In ActiveSheet.PivotTables
pt1.PivotFields(fieldadd(1, 2)). _
CurrentPage = target.Value
Next pt1
On Error Resume Next
Next pt
End If

target.Select
Application.ScreenUpdating = True
End Sub

This code basically has been written within the worksheet_change function which is an inbuilt function that gets triggered everytime a cell change occurs in an Excel worksheet. To synchronize two or more pivots we simply create a master drop-down using data validation. We then use this cell drop-down to choose between the pivot tables values.

What we are essentially doing here is to check which cell has changed and if that cell happens to be the one that has the master drop-down list. If yes, we simply take the value selected and make all the pivots reflect the same value. A couple of important points:

1. Ensure that only valid values are present in the master drop-down and
2. Ensure that “All” is a data item in the master drop-down.

Obviously this code can be further enhanced to ensure that it has better error handling abilities. This is just a general outline here of how two or more pivot tables can be synchronized.