Kathy L. wrote on 04/09/2010 18:22 ET :
Hello experts,
I've reviewed PT0021 Change All Page Fields sample Excel file (for 2007) on
http://www.contextures.com/excelfiles.html#Pivot and am still having trouble
with my complex workbook with several pivot tables/charts. I have found in
this sample file that it works when you choose an explicit value in the
filter, however when you re-set the filters back to 'all' or choose multiple
values, this does not apply to subsequent pivot tables and worksheets. Is
this only possible with explicit values in the main filter or can the code be
modified to work when choosing 'all'?

Also, my workbook contains several worksheets of data, and subsequent
worksheets of various pivot tables for each set of data. The field names
would be consistent across each worksheet of data, however in my ideal world,
I'd like one main set of filters for a main pivot table to control ALL pivot
tables on subsequent worksheets, which feed from a variety of data worksheets
(all in the same workbook). Am I dreaming? So far, Excel pros that I've
solicited help from using the sample file referenced above have not been able
to make this work. Thank you.

Did y'all ever find a way around the issue Kathy L had with PT0021? I adapted
the code for my 2 sheet workbook and added a button (as in following code).
However, I also see that when I enable “Select Multiple Items” in
a
pagefield and pick a few criteria, the ‘slave’ pivots do not
follow
the ‘master’. Since I’m bumbling along here, borrowing code
as
I can, I’ve hit a wall on how to fix that. If you could give me a push
in
right direction, I’d appreciate it. I also posted on the Microsoft
Development Network site as a question, and once I’ve fixed, I’ll
put that up too (of course giving Debra et al the original credit)! I’m
hoping you have already addressed this and it won’t inconvenience you
further to pass on to me. Best Regards, David Shugart
-------------------
Private Sub CommandButton1_Click()

On Error Resume Next
Dim ws As Worksheet
Dim wsMain As Worksheet
Dim ptMain As PivotTable
Dim pt As PivotTable
Dim pfMain As PivotField
Dim pi As PivotItem
Dim pf As PivotField

On Error Resume Next
Set wsMain = Sheets("Sales Pivot")
Set ws = Sheets("Pivots")
Set ptMain = ActiveSheet.PivotTables("PivotTable4")

Application.EnableEvents = False
Application.ScreenUpdating = False

For Each pfMain In ptMain.PageFields
If ws.Name < wsMain.Name Then
For Each pt In ws.PivotTables
pt.RefreshTable
For Each pf In pt.PageFields
If pf.Name = pfMain.Name Then
If pfMain.CurrentPage = "(All)" Then
pf.CurrentPage = "(All)"
Exit For
End If
For Each pi In pf.PivotItems
If pi.Name = pfMain.CurrentPage Then
pf.CurrentPage = pi.Name
Exit For
End If
Next pi
End If
Next pf
Next pt
End If
Next pfMain

Application.EnableEvents = True
Application.ScreenUpdating = True

End Sub