Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 1
Default 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

  #2   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 1
Default synchronising pivot tables


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?


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

Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
Need to create multiple Pivot tables at once havocdragon Excel Discussion (Misc queries) 3 July 27th 06 11:16 PM
#REF in Sheets that refer to Pivot Tables Will C. Excel Discussion (Misc queries) 1 June 2nd 06 08:54 PM
Refreshing Pivot Tables linked to Oracle Datasource [email protected] Excel Discussion (Misc queries) 0 April 25th 06 12:15 AM
Dynamically changing several pivot tables at once Jason Excel Discussion (Misc queries) 3 December 16th 05 04:50 PM
Macro for Pivot Tables Thomas Excel Discussion (Misc queries) 1 March 15th 05 02:03 AM


All times are GMT +1. The time now is 09:20 PM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"