Filtering data from one pivot table against another

I have multiple pivot tables and I want to carry over the selections from
Pivot table a to Pivot table B, so that the page fields are filtered
identically. Any ideas?

The following code is adapted from a posting by Robert Rosenberg. It
changes the second Pivot Table if the page is changed on the first PT.
You could revise it to suit your layout. As noted in the code, place the
code on the module for the worksheet which contains the Pivot Table
(right-click the sheet tab, choose View Code).
'================================
Dim mvPivotPageValue As Variant

Private Sub Worksheet_Calculate()
'by Robert Rosenberg 2000/01/11
''I use a module level variable to keep track of
''the last selection from the Page Field.
''This routine was place in the Worksheet
''containing the PivotTable's code module.
Dim pvt As PivotTable
Dim pvt2 As PivotTable

Set pvt = Me.PivotTables(1)
Set pvt2 = Sheets("OtherPivot").PivotTables(1)
If LCase(pvt.PivotFields("Customer").CurrentPage) _
<> LCase(mvPivotPageValue) Then
'The Page Field was changed
Application.EnableEvents = False
pvt.RefreshTable
mvPivotPageValue = _
pvt.PivotFields("Customer").CurrentPage
pvt2.PageFields("Customer").CurrentPage _
= mvPivotPageValue
Application.EnableEvents = True
End If

End Sub
'================================

RobinG wrote:
> I have multiple pivot tables and I want to carry over the selections from
> Pivot table a to Pivot table B, so that the page fields are filtered
> identically. Any ideas?


--
Debra Dalgleish
Excel FAQ, Tips & Book List
http://www.contextures.com/tiptech.html

Works great! Thank you.

"Debra Dalgleish" wrote:

> The following code is adapted from a posting by Robert Rosenberg. It
> changes the second Pivot Table if the page is changed on the first PT.
> You could revise it to suit your layout. As noted in the code, place the
> code on the module for the worksheet which contains the Pivot Table
> (right-click the sheet tab, choose View Code).
> '================================
> Dim mvPivotPageValue As Variant
>
> Private Sub Worksheet_Calculate()
> 'by Robert Rosenberg 2000/01/11
> ''I use a module level variable to keep track of
> ''the last selection from the Page Field.
> ''This routine was place in the Worksheet
> ''containing the PivotTable's code module.
> Dim pvt As PivotTable
> Dim pvt2 As PivotTable
>
> Set pvt = Me.PivotTables(1)
> Set pvt2 = Sheets("OtherPivot").PivotTables(1)
> If LCase(pvt.PivotFields("Customer").CurrentPage) _
> <> LCase(mvPivotPageValue) Then
> 'The Page Field was changed
> Application.EnableEvents = False
> pvt.RefreshTable
> mvPivotPageValue = _
> pvt.PivotFields("Customer").CurrentPage
> pvt2.PageFields("Customer").CurrentPage _
> = mvPivotPageValue
> Application.EnableEvents = True
> End If
>
> End Sub
> '================================
>
> RobinG wrote:
> > I have multiple pivot tables and I want to carry over the selections from
> > Pivot table a to Pivot table B, so that the page fields are filtered
> > identically. Any ideas?
>
>
> --
> Debra Dalgleish
> Excel FAQ, Tips & Book List
> http://www.contextures.com/tiptech.html
>
>

Debra,

I spoke a little soon. It works as long as I change the page fields top to
bottom.

I have 4 different page fields I'm trying to perform this operation on. If
I go top to bottom it works fine, but if I change them out of order the upper
fields don't change.

Here is my code

===================
Private Sub Worksheet_Calculate()
'by Robert Rosenberg 2000/01/11
''I use a module level variable to keep track of
''the last selection from the Page Field.
''This routine was place in the Worksheet
''containing the PivotTable's code module.
Dim pvt As PivotTable
Dim pvt2 As PivotTable

Set pvt = Me.PivotTables(1)
Set pvt2 = Sheets("Turns Year").PivotTables(1)
If LCase(pvt.PivotFields("Business Group").CurrentPage) _
<> LCase(mvPivotPageValue) Then
'The Page Field was changed
Application.EnableEvents = False
pvt.RefreshTable
mvPivotPageValue = _
pvt.PivotFields("Business Group").CurrentPage
pvt2.PageFields("Business Group").CurrentPage _
= mvPivotPageValue
Application.EnableEvents = True
End If
If LCase(pvt.PivotFields("Business").CurrentPage) _
<> LCase(mvPivotPageValue) Then
'The Page Field was changed
Application.EnableEvents = False
pvt.RefreshTable
mvPivotPageValue = _
pvt.PivotFields("Business").CurrentPage
pvt2.PageFields("Business").CurrentPage _
= mvPivotPageValue
Application.EnableEvents = True
End If
If LCase(pvt.PivotFields("Envelope").CurrentPage) _
<> LCase(mvPivotPageValue) Then
'The Page Field was changed
Application.EnableEvents = False
pvt.RefreshTable
mvPivotPageValue = _
pvt.PivotFields("Envelope").CurrentPage
pvt2.PageFields("Envelope").CurrentPage _
= mvPivotPageValue
Application.EnableEvents = True
End If
If LCase(pvt.PivotFields("Product Family").CurrentPage) _
<> LCase(mvPivotPageValue) Then
'The Page Field was changed
Application.EnableEvents = False
pvt.RefreshTable
mvPivotPageValue = _
pvt.PivotFields("Product Family").CurrentPage
pvt2.PageFields("Product Family").CurrentPage _
= mvPivotPageValue
Application.EnableEvents = True
End If
End Sub

"RobinG" wrote:

> Works great! Thank you.
>
> "Debra Dalgleish" wrote:
>
> > The following code is adapted from a posting by Robert Rosenberg. It
> > changes the second Pivot Table if the page is changed on the first PT.
> > You could revise it to suit your layout. As noted in the code, place the
> > code on the module for the worksheet which contains the Pivot Table
> > (right-click the sheet tab, choose View Code).
> > '================================
> > Dim mvPivotPageValue As Variant
> >
> > Private Sub Worksheet_Calculate()
> > 'by Robert Rosenberg 2000/01/11
> > ''I use a module level variable to keep track of
> > ''the last selection from the Page Field.
> > ''This routine was place in the Worksheet
> > ''containing the PivotTable's code module.
> > Dim pvt As PivotTable
> > Dim pvt2 As PivotTable
> >
> > Set pvt = Me.PivotTables(1)
> > Set pvt2 = Sheets("OtherPivot").PivotTables(1)
> > If LCase(pvt.PivotFields("Customer").CurrentPage) _
> > <> LCase(mvPivotPageValue) Then
> > 'The Page Field was changed
> > Application.EnableEvents = False
> > pvt.RefreshTable
> > mvPivotPageValue = _
> > pvt.PivotFields("Customer").CurrentPage
> > pvt2.PageFields("Customer").CurrentPage _
> > = mvPivotPageValue
> > Application.EnableEvents = True
> > End If
> >
> > End Sub
> > '================================
> >
> > RobinG wrote:
> > > I have multiple pivot tables and I want to carry over the selections from
> > > Pivot table a to Pivot table B, so that the page fields are filtered
> > > identically. Any ideas?
> >
> >
> > --
> > Debra Dalgleish
> > Excel FAQ, Tips & Book List
> > http://www.contextures.com/tiptech.html
> >
> >

I don't understand what you mean. Can you give an example of what's
happening, and what you expect to happen?

RobinG wrote:
> Debra,
>
> I spoke a little soon. It works as long as I change the page fields top to
> bottom.
>
> I have 4 different page fields I'm trying to perform this operation on. If
> I go top to bottom it works fine, but if I change them out of order the upper
> fields don't change.
>
> Here is my code
>
> ===================
> Private Sub Worksheet_Calculate()
> 'by Robert Rosenberg 2000/01/11
> ''I use a module level variable to keep track of
> ''the last selection from the Page Field.
> ''This routine was place in the Worksheet
> ''containing the PivotTable's code module.
> Dim pvt As PivotTable
> Dim pvt2 As PivotTable
>
> Set pvt = Me.PivotTables(1)
> Set pvt2 = Sheets("Turns Year").PivotTables(1)
> If LCase(pvt.PivotFields("Business Group").CurrentPage) _
> <> LCase(mvPivotPageValue) Then
> 'The Page Field was changed
> Application.EnableEvents = False
> pvt.RefreshTable
> mvPivotPageValue = _
> pvt.PivotFields("Business Group").CurrentPage
> pvt2.PageFields("Business Group").CurrentPage _
> = mvPivotPageValue
> Application.EnableEvents = True
> End If
> If LCase(pvt.PivotFields("Business").CurrentPage) _
> <> LCase(mvPivotPageValue) Then
> 'The Page Field was changed
> Application.EnableEvents = False
> pvt.RefreshTable
> mvPivotPageValue = _
> pvt.PivotFields("Business").CurrentPage
> pvt2.PageFields("Business").CurrentPage _
> = mvPivotPageValue
> Application.EnableEvents = True
> End If
> If LCase(pvt.PivotFields("Envelope").CurrentPage) _
> <> LCase(mvPivotPageValue) Then
> 'The Page Field was changed
> Application.EnableEvents = False
> pvt.RefreshTable
> mvPivotPageValue = _
> pvt.PivotFields("Envelope").CurrentPage
> pvt2.PageFields("Envelope").CurrentPage _
> = mvPivotPageValue
> Application.EnableEvents = True
> End If
> If LCase(pvt.PivotFields("Product Family").CurrentPage) _
> <> LCase(mvPivotPageValue) Then
> 'The Page Field was changed
> Application.EnableEvents = False
> pvt.RefreshTable
> mvPivotPageValue = _
> pvt.PivotFields("Product Family").CurrentPage
> pvt2.PageFields("Product Family").CurrentPage _
> = mvPivotPageValue
> Application.EnableEvents = True
> End If
> End Sub
>
> "RobinG" wrote:
>
>
>>Works great! Thank you.
>>
>>"Debra Dalgleish" wrote:
>>
>>
>>>The following code is adapted from a posting by Robert Rosenberg. It
>>>changes the second Pivot Table if the page is changed on the first PT.
>>>You could revise it to suit your layout. As noted in the code, place the
>>>code on the module for the worksheet which contains the Pivot Table
>>>(right-click the sheet tab, choose View Code).
>>>'================================
>>>Dim mvPivotPageValue As Variant
>>>
>>>Private Sub Worksheet_Calculate()
>>>'by Robert Rosenberg 2000/01/11
>>>''I use a module level variable to keep track of
>>>''the last selection from the Page Field.
>>>''This routine was place in the Worksheet
>>>''containing the PivotTable's code module.
>>>Dim pvt As PivotTable
>>>Dim pvt2 As PivotTable
>>>
>>>Set pvt = Me.PivotTables(1)
>>>Set pvt2 = Sheets("OtherPivot").PivotTables(1)
>>>If LCase(pvt.PivotFields("Customer").CurrentPage) _
>>> <> LCase(mvPivotPageValue) Then
>>> 'The Page Field was changed
>>> Application.EnableEvents = False
>>> pvt.RefreshTable
>>> mvPivotPageValue = _
>>> pvt.PivotFields("Customer").CurrentPage
>>> pvt2.PageFields("Customer").CurrentPage _
>>> = mvPivotPageValue
>>> Application.EnableEvents = True
>>>End If
>>>
>>>End Sub
>>>'================================
>>>
>>>RobinG wrote:
>>>
>>>>I have multiple pivot tables and I want to carry over the selections from
>>>>Pivot table a to Pivot table B, so that the page fields are filtered
>>>>identically. Any ideas?
>>>
>>>
>>>--
>>>Debra Dalgleish
>>>Excel FAQ, Tips & Book List
>>>http://www.contextures.com/tiptech.html
>>>
>>>
>>


--
Debra Dalgleish
Excel FAQ, Tips & Book List
http://www.contextures.com/tiptech.html