synchronising pivot tables

[Johngio]

Hi all,

I basically am trying to sychronise some pivots tables , such that when I update one cell it updates the same cell in all tables. I have 7 pivot tables which I want to update simultaneously. I have to write a macro to do this, but I lack the expertise :P

I have found some code wihch does a similar thing, however I have tried manipulating this code to get it to work to no avail. As far as I can tell that is what this code does but I'm not sure if I have to put each pivot table on a separate worksheet and embed the lower code in each sheet. It references cell B1, so I assume in each of these worksheets the input cell has to be placed at B2. Also, it refers to PERSON as the pivot fields; mine is Dates, so I assume I change this. finally, I can't figure out how it tells which cell to update from. If this macro is placed on the first worksheet and I update this worksheet, will it automatically update the others?

I have tried a few things but they're not updating when I run the macro?

Here is the code:

'===========================================================
'- SHARED ROUTINE TO REFRESH TABLES
'===========================================================
Public SelectedPageField As Variant
'-----------------------------------
'- main macro
Sub RefreshAllTables()
'- temporarily stop other things happening
Application.EnableEvents = False
Application.DisplayAlerts = False
Application.Calculation = xlCalculationManual
On Error Resume Next
'- main loop
For Each ws In Worksheets
For Each pt In ws.PivotTables
pt.RefreshTable
pt.PivotFields("PERSON").CurrentPage = SelectedPageField
Next
Next
'- restore environment
Application.DisplayAlerts = True
Application.Calculation = xlCalculationAutomatic
Application.EnableEvents = True
End Sub


'=============================================================
'- CODE IN EACH WORKSHEET MODULE
'- right click tab & 'View code'
'- may require =NOW() in a cell somewhere to force calculation
'=============================================================
Private Sub Worksheet_Calculate()
If ActiveCell.Address = "$B$2" Then ' PageField address
SelectedPageField = ActiveCell.Value
RefreshAllTables
End If
End Sub


Any ideas ? ? ? Or does anyone know of a different way of synchronising pivot tables ???

Cheers

John

[steven1001]

You do not say if all the pivots are from the same dataset.
If it is, and the data is in a named range [insert > name > define], if you refresh one pivot table then all are updated. Is this what you mean?