Prev: Lost X-axis after resizing charts using VB - not sure why...
Next: Running Excel 12.0 Application on Office 2003
From: Cam on 6 May 2010 13:40
Hello,

I have 2 pivot tables on the same worksheet. The WCtr field is a user select
field so when a WCtr in pivottable1 choice is selected, I want to automate
the same selection in other pivotable2.
I got the following code, but it didn't update pivot2 when pivot1 is
filtered. Please help with the code. Thanks

Private Sub Worksheet_PivotTableUpdate(ByVal Target As PivotTable)
'
' Macro created 5/18/2009 by ge295c
'
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("WCtr").CurrentPage
source.PivotFields("WCtr").CurrentPage = text

Application.EnableEvents = True

End Sub
From: Bob Umlas, Excel MVP on 6 May 2010 16:02
Have a look here:
http://www.datapigtechnologies.com/flashfiles/pivot10.html

"Cam" wrote:

> Hello,
>
> I have 2 pivot tables on the same worksheet. The WCtr field is a user select
> field so when a WCtr in pivottable1 choice is selected, I want to automate
> the same selection in other pivotable2.
> I got the following code, but it didn't update pivot2 when pivot1 is
> filtered. Please help with the code. Thanks
>
> Private Sub Worksheet_PivotTableUpdate(ByVal Target As PivotTable)
> '
> ' Macro created 5/18/2009 by ge295c
> '
> 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("WCtr").CurrentPage
> source.PivotFields("WCtr").CurrentPage = text
>
> Application.EnableEvents = True
>
> End Sub
 | 
Pages: 1
Prev: Lost X-axis after resizing charts using VB - not sure why...
Next: Running Excel 12.0 Application on Office 2003