pivot table comparison

Ok new question! I've been trying to work this out, but i havent been
successful. I want when i select the Quarters to be displayed on one pivot
table, that those quarters be the same ones displayed on all the pivot
tables. I know i have to use PivotTable("name"), PivotField("Quarters") and
then use PivotItems.visible = true. I have to have one for my source data
and then one that will be changing to do the comparison, but I'm not exactly
sure on how to loop through the pivotitems and compare that to my main
pivotitems. If you need a better explanation please let me know, these pivot
table issues are pretty complex.

Thanks,
Tim

There's a sample file here that changes multiple pivot tables, based on
selections in the page field of the main pivot table:

http://www.contextures.com/excelfiles.html

Under Pivot Tables, look for 'Change Page Field'

Tim wrote:
> Ok new question! I've been trying to work this out, but i havent been
> successful. I want when i select the Quarters to be displayed on one pivot
> table, that those quarters be the same ones displayed on all the pivot
> tables. I know i have to use PivotTable("name"), PivotField("Quarters") and
> then use PivotItems.visible = true. I have to have one for my source data
> and then one that will be changing to do the comparison, but I'm not exactly
> sure on how to loop through the pivotitems and compare that to my main
> pivotitems. If you need a better explanation please let me know, these pivot
> table issues are pretty complex.
>
> Thanks,
> Tim


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

Debra,
I've implemented that already into the solution. I'm going to use that page
field example to illustrate what i'm wanting to do. Under sales Pivot i want
to select the Employee record, say Gil, Howard and Jones and that be the only
records being shown on the other pivot tables as well. Since the Item is a
drop down box, only one item can be selected. This is now a list but i cant
seem to figure out how to just say currentlist1 = maincurrentlist.

"Debra Dalgleish" wrote:

> There's a sample file here that changes multiple pivot tables, based on
> selections in the page field of the main pivot table:
>
> http://www.contextures.com/excelfiles.html
>
> Under Pivot Tables, look for 'Change Page Field'
>
> Tim wrote:
> > Ok new question! I've been trying to work this out, but i havent been
> > successful. I want when i select the Quarters to be displayed on one pivot
> > table, that those quarters be the same ones displayed on all the pivot
> > tables. I know i have to use PivotTable("name"), PivotField("Quarters") and
> > then use PivotItems.visible = true. I have to have one for my source data
> > and then one that will be changing to do the comparison, but I'm not exactly
> > sure on how to loop through the pivotitems and compare that to my main
> > pivotitems. If you need a better explanation please let me know, these pivot
> > table issues are pretty complex.
> >
> > Thanks,
> > Tim
>
>
> --
> Debra Dalgleish
> Excel FAQ, Tips & Book List
> http://www.contextures.com/tiptech.html
>
>

You could move the page field to the row area in the code, hide matching
items in the other pivot tables, then move the field back to the page
area. For example:

'============================
Sub PivotHidePageItems()
Dim wsSP As Worksheet
Dim wsOP As Worksheet
Dim ptMain As PivotTable
Dim pfMain As PivotField
Dim pt As PivotTable
Dim pf As PivotField
Dim pi As PivotItem
Dim strField As String
Set wsSP = Sheets("Sales Pivot")
Set wsOP = Sheets("Other Pivots")
Set ptMain = wsSP.PivotTables(1)
strField = "Item"
Set pfMain = ptMain.PivotFields(strField)

Application.ScreenUpdating = False
Application.DisplayAlerts = False
Application.EnableEvents = False

With pfMain
.Orientation = xlRowField
.Position = 1
End With

For Each pt In wsOP.PivotTables
Set pf = pt.PivotFields(strField)
On Error Resume Next
With pf
.CurrentPage = "(All)"
.AutoSort xlManual, .SourceName
'show all pivot items
For Each pi In pf.PivotItems
pi.Visible = True
Next pi
'hide pivot items to match main table
For Each pi In pf.PivotItems
If pfMain.PivotItems(pi.Name).Visible = False Then
pi.Visible = False
End If
Next pi
.AutoSort xlAscending, .SourceName
End With
Next pt

With pfMain
.Orientation = xlPageField
.Position = 1
End With

Application.DisplayAlerts = True
Application.ScreenUpdating = True
Application.EnableEvents = True

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


Tim wrote:
> Debra,
> I've implemented that already into the solution. I'm going to use that page
> field example to illustrate what i'm wanting to do. Under sales Pivot i want
> to select the Employee record, say Gil, Howard and Jones and that be the only
> records being shown on the other pivot tables as well. Since the Item is a
> drop down box, only one item can be selected. This is now a list but i cant
> seem to figure out how to just say currentlist1 = maincurrentlist.
>
> "Debra Dalgleish" wrote:
>
>
>>There's a sample file here that changes multiple pivot tables, based on
>>selections in the page field of the main pivot table:
>>
>> http://www.contextures.com/excelfiles.html
>>
>>Under Pivot Tables, look for 'Change Page Field'
>>
>>Tim wrote:
>>
>>>Ok new question! I've been trying to work this out, but i havent been
>>>successful. I want when i select the Quarters to be displayed on one pivot
>>>table, that those quarters be the same ones displayed on all the pivot
>>>tables. I know i have to use PivotTable("name"), PivotField("Quarters") and
>>>then use PivotItems.visible = true. I have to have one for my source data
>>>and then one that will be changing to do the comparison, but I'm not exactly
>>>sure on how to loop through the pivotitems and compare that to my main
>>>pivotitems. If you need a better explanation please let me know, these pivot
>>>table issues are pretty complex.
>>>
>>>Thanks,
>>>Tim
>>
>>
>>--
>>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