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

pivot table comparison
 

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

pivot table comparison
 

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

pivot table comparison
 

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