Pivot table help

  • Thread starter Thread starter Cam
  • Start date Start date
C

Cam

Hello,

I have two pivot table on a same sheet with the same drop down list on the
top selection (not row or column).

Is it possible to select from a selection list from one pivot and
automatically select the same selection list from the second pivot table? If
it is how to achieve it. Thanks
 
Hi Patrick,

Thank for the sugguestion. I tried your code and it is not updating the
other pivot table 2 when the pivot table 1 field is selected. Do I put the
code in a module or where? Any way, this is the code I put in a module.

Sub Worksheet_PivotTableUpdate(ByVal Target As PivotTable)
Dim source As PivotTable
Dim text As String

'check which table changed, we'll make the same change to the other
If Target.Name = "PivotTable1" Then
Set source = PivotTables("PivotTable2")
Else
Set source = PivotTables("PivotTable1")
End If

Application.EnableEvents = False

text = Target.PivotFields("WC").CurrentPage
source.PivotFields("WC").CurrentPage = text

Application.EnableEvents = True

End Sub
 
Thanks for your help, I got it.

Cam said:
Hi Patrick,

Thank for the sugguestion. I tried your code and it is not updating the
other pivot table 2 when the pivot table 1 field is selected. Do I put the
code in a module or where? Any way, this is the code I put in a module.

Sub Worksheet_PivotTableUpdate(ByVal Target As PivotTable)
Dim source As PivotTable
Dim text As String

'check which table changed, we'll make the same change to the other
If Target.Name = "PivotTable1" Then
Set source = PivotTables("PivotTable2")
Else
Set source = PivotTables("PivotTable1")
End If

Application.EnableEvents = False

text = Target.PivotFields("WC").CurrentPage
source.PivotFields("WC").CurrentPage = text

Application.EnableEvents = True

End Sub
Click to expand...
 
write to me at patrick_molloy_NOSPAMhotmail.com and I'll fwd the excel
workbook
remove the NOSPAM bit ;)
 
Patrick,

Thanks for your help. I do have another question.

What do I need to change in the code if pivottable1 and pivottable2 are in a
different worksheet?
 
its easier since you don't need to use the IF to determine which pivot table
changed

Sub Worksheet_PivotTableUpdate(ByVal Target As PivotTable)
Dim source As PivotTable
Dim text As String

Set source = PivotTables("PivotTable2")
Application.EnableEvents = False
text = Target.PivotFields("WC").CurrentPage
source.PivotFields("WC").CurrentPage = text
Application.EnableEvents = True

End Sub


for the sheet containing pivottable2

change
Set source = PivotTables("PivotTable2")

to
Set source = PivotTables("PivotTable1")
 
Back
Top