synchronising pivot tables

[Johngio]

Hi all,

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

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

I have tried a few things but they're not updating when I run th
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 synchronisin
pivot tables ???

Cheers

Joh

 

[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?