Pivot table help
 

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

Pivot table help
 

the code belongs to the sheet's code page.

"Cam" wrote in message
...
Thanks for your help, I got it.

"Cam" wrote:

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

"Patrick Molloy" wrote:

this can be done. My example isn't 'pretty' but will give the general
idea

in the sheet events, you can trap the Pivot Table Update Event

so i have two tables where the page field that I'm changing is in the
pivot
field called 'Set' in both pivots....the fields can differ, so long as
the
data is the same

The event passes the changed pivot through to the handler in the
variable
named 'Target'
I check which table changes, and set a pivot table object, Source, to
the
other pivot table

Now, when I select a value from the page drop down on either pivot,
when
that pivot refreshes, the handler maked the same change to the other
pivot.
Note that this 2nd change would also fire the event handler, so we'd go
into
an infinite loop -- so i switch event handling off for this change,
then
switch it on again afterwatds

Here's my code:

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

'check whic 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("SET").CurrentPage
source.PivotFields("SET").CurrentPage = text

Application.EnableEvents = True

End Sub

"Cam" wrote in message
...
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

Pivot table help
 

write to me at patrick_molloy_NOSPAMhotmail.com and I'll fwd the excel
workbook
remove the NOSPAM bit ;)



"Cam" wrote in message
...
Thanks for your help, I got it.

"Cam" wrote:

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

"Patrick Molloy" wrote:

this can be done. My example isn't 'pretty' but will give the general
idea

in the sheet events, you can trap the Pivot Table Update Event

so i have two tables where the page field that I'm changing is in the
pivot
field called 'Set' in both pivots....the fields can differ, so long as
the
data is the same

The event passes the changed pivot through to the handler in the
variable
named 'Target'
I check which table changes, and set a pivot table object, Source, to
the
other pivot table

Now, when I select a value from the page drop down on either pivot,
when
that pivot refreshes, the handler maked the same change to the other
pivot.
Note that this 2nd change would also fire the event handler, so we'd go
into
an infinite loop -- so i switch event handling off for this change,
then
switch it on again afterwatds

Here's my code:

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

'check whic 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("SET").CurrentPage
source.PivotFields("SET").CurrentPage = text

Application.EnableEvents = True

End Sub

"Cam" wrote in message
...
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

Pivot table help
 

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?

"Patrick Molloy" wrote:

write to me at patrick_molloy_NOSPAMhotmail.com and I'll fwd the excel
workbook
remove the NOSPAM bit ;)



"Cam" wrote in message
...
Thanks for your help, I got it.

"Cam" wrote:

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

"Patrick Molloy" wrote:

this can be done. My example isn't 'pretty' but will give the general
idea

in the sheet events, you can trap the Pivot Table Update Event

so i have two tables where the page field that I'm changing is in the
pivot
field called 'Set' in both pivots....the fields can differ, so long as
the
data is the same

The event passes the changed pivot through to the handler in the
variable
named 'Target'
I check which table changes, and set a pivot table object, Source, to
the
other pivot table

Now, when I select a value from the page drop down on either pivot,
when
that pivot refreshes, the handler maked the same change to the other
pivot.
Note that this 2nd change would also fire the event handler, so we'd go
into
an infinite loop -- so i switch event handling off for this change,
then
switch it on again afterwatds

Here's my code:

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

'check whic 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("SET").CurrentPage
source.PivotFields("SET").CurrentPage = text

Application.EnableEvents = True

End Sub

"Cam" wrote in message
...
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

Pivot table help
 

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")



"Cam" wrote in message
...
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?

"Patrick Molloy" wrote:

write to me at patrick_molloy_NOSPAMhotmail.com and I'll fwd the excel
workbook
remove the NOSPAM bit ;)



"Cam" wrote in message
...
Thanks for your help, I got it.

"Cam" wrote:

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

"Patrick Molloy" wrote:

this can be done. My example isn't 'pretty' but will give the
general
idea

in the sheet events, you can trap the Pivot Table Update Event

so i have two tables where the page field that I'm changing is in
the
pivot
field called 'Set' in both pivots....the fields can differ, so long
as
the
data is the same

The event passes the changed pivot through to the handler in the
variable
named 'Target'
I check which table changes, and set a pivot table object, Source,
to
the
other pivot table

Now, when I select a value from the page drop down on either pivot,
when
that pivot refreshes, the handler maked the same change to the other
pivot.
Note that this 2nd change would also fire the event handler, so we'd
go
into
an infinite loop -- so i switch event handling off for this change,
then
switch it on again afterwatds

Here's my code:

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

'check whic 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("SET").CurrentPage
source.PivotFields("SET").CurrentPage = text

Application.EnableEvents = True

End Sub

"Cam" wrote in message
...
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