linked pivot tables

Yes, you can do this using the _Change() event of the worksheet. Have you used macros ?

On 2002-12-09 23:12, Juan Pablo G. wrote:
Yes, you can do this using the _Change() event of the worksheet. Have you used macros ?

Click to expand...

I would love to know how this can be done

i have not use the macros before but am familiar with Access VB. would you put the change worksheet with the module or the worksheet???

Hi Juan,

i have piece together after browsing through this and other forums. But it does not seem to work. Here's my code attached to the worksheet with the pivot tables.

Sub Worksheet_Change(ByVal Target As Range)
'synchronize L3 parent location between pivot2 dependent pivot3 pivot4
'Find name of page field
'PivotTable2 is the lead pivot table
MsgBox "hello"
PageField2 = ActiveSheet.PivotTables("PivotTable2").PageFields(1)
pvt2page = ActiveSheet.PivotTables("PivotTable2").PivotFields(PageFields1).CurrentPage

PageField3 = ActiveSheet.PivotTables("PivotTable3").PageFields(1)
ActiveSheet.PivotTables("PivotTable3").PivotFields(PageFields3).CurrentPage = pvt2page

PageField4 = ActiveSheet.PivotTables("PivotTable4").PageFields(1)
ActiveSheet.PivotTables("PivotTable4").PivotFields(PageFields4).CurrentPage = pvt2page

End Sub

Changing the Page Field does not seem to trigger either the Worksheet_Change event or the Worksheet_SelectionChange event. But this worked for me:

hi juan,
a change in the pivot table page dropdown does not seem to trigger the worksheet_change event ????? workarounds???

help!

hi andrew,
this works! thanks!!!!
peter

Hi gus this looked like a neat solution to a problem I had of linking three pivot tables each with 5 page fields. However, when I put the code (below) nothing happened. In fact when I ran the code I got the run-time error 1004 message saying application define or object defined error.

Any help

Cheers
Mark

Private Sub Worksheet_Calculate()

Dim PF1 As PivotFields
Dim PF2 As PivotFields
Dim PF3 As PivotFields
Dim X As String

Application.EnableEvents = False
Application.ScreenUpdating = False

Set PF1 = ActiveSheet.PivotTables("PivotTable3").PageFields
Set PF2 = ActiveSheet.PivotTables("PivotTable1").PageFields
Set PF3 = ActiveSheet.PivotTables("PivotTable4").PageFields

X = PF1.CurrentPage
PF2.CurrentPage = X
PF3.CurrentPage = X

Application.ScreenUpdating = True
Application.EnableEvents = True

End Sub

maabbot said:

Private Sub Worksheet_Calculate()

Dim PF1 As PivotFields
Dim PF2 As PivotFields
Dim PF3 As PivotFields
Dim X As String

Application.EnableEvents = False
Application.ScreenUpdating = False

Set PF1 = ActiveSheet.PivotTables("PivotTable3").PageFields
Set PF2 = ActiveSheet.PivotTables("PivotTable1").PageFields
Set PF3 = ActiveSheet.PivotTables("PivotTable4").PageFields

X = PF1.CurrentPage
PF2.CurrentPage = X
PF3.CurrentPage = X

Application.ScreenUpdating = True
Application.EnableEvents = True

End Sub

Click to expand...

Just came across this and wondered if it can be modified so that should you "Select Multiple Items" the selection would be replicated on the second pivot.

Martin