ExcelBanter - synchronising pivot tables

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

--
Johngio
------------------------------------------------------------------------
Johngio's Profile: http://www.excelforum.com/member.php...o&userid=36940
View this thread: http://www.excelforum.com/showthread...hreadid=566504