Single Filter to change all Pivot Tables in a Workbook

[Kathy L.]

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.

 

[Roger Govier]

Hi Kathy

I think the problem is that (All) is not a pivot item when the code is
cycling through the list of items belonging to a field.

If you modify Debra's code to include the following 4 lines, then it
will do what you want

For Each pf In pt.PageFields
If pf.Name = pfMain.Name Then
For Each pi In pf.PivotItems

' inserted lines
If pfMain.CurrentPage = "(All)" Then
pf.CurrentPage = "(All)"
Exit For
End If
' end of inserted lines
If pi.Name = pfMain.CurrentPage Then
pf.CurrentPage = pi.Name
Exit For
End If
Next pi
End If

I will drop Debra a note of this change, and if I am correct, then I am
sure that she will modify her file on the website.

 

[Herbert Seidenberg]

Excel 2007 PivotTable
Update Page Fields of multiple PTs.
With "Select Multiple Items".
With macro.
http://c0718892.cdn.cloudfiles.rackspacecloud.com/04_10_10.xlsm
Pdf preview:
http://www.mediafire.com/file/znynzumj3jn/04_10_10.pdf
For help on multiple sheets, upload your file.

 

[Roger Govier]

Hi Herbert

Regrettably, it fails each time for me with error 1004 on the line
pvi.Visible = b(r, p, 2)

 

[Herbert Seidenberg]

Roger,
Could not duplicate error,
but the index numbers of the PTs were scrambled.
See Test().
Changed the line after
For u=2 to t
So now index numbers are ignored.
Same cloud link.
A collaboration on Kathy's #2 problem would be fun.
Herb

 

[Roger Govier]

Hi Herbert

Still getting the problem.
It errors 1004, Unable to set the visible property of the pivot item.
It seems to me, that if the value of the item is already the same as
what it is trying to be set, then it errors.

Inserting 3 lines

If pvi.Name = b(r, p, 1) Then

If pvi.Visible <> b(r, p, 2) Then
pvi.Visible = b(r, p, 2)
End If

End If

enables it to run through for me.

I still get problems though, as PT2 fails to get updated at all.
I think we had best take this off line.

You can email me at
roger at technology4u dot co dot uk
Change the at and dots to make valid email address

 

[Herbert Seidenberg]

Roger,
My email attempts were blocked.
The errors you correctly identified were dealt with
in the current version (find PT6).
Please download the file at the original cloud link.
Herb

 

[Kathy L.]

Roger & Herbert - thank you both for your efforts. I apologize I didn't see
these replies sooner. If you're willing to collaborate and take a look at
the 'challenge' what is the best way to get you a file? It's likely large to
get you the full extent of what I'm trying to tackle. Thank you.

 

[Roger Govier]

Hi Kathy and Herbert

My attempts at emailing Herbert direct also failed.
I know he had problems sending to me - but I don't understand why.
Both of you can try again.

roger at technology4u dot co dot uk

Change the at to @ and the dot to . and eliminate all spaces.

 

[davids]

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) o
http://www.contextures.com/excelfiles.html#Pivot and am still having troubl
with my complex workbook with several pivot tables/charts. I have found i
this sample file that it works when you choose an explicit value in th
filter, however when you re-set the filters back to 'all' or choose multipl
values, this does not apply to subsequent pivot tables and worksheets. I
this only possible with explicit values in the main filter or can the code b
modified to work when choosing 'all'

Also, my workbook contains several worksheets of data, and subsequen
worksheets of various pivot tables for each set of data. The field name
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 pivo
tables on subsequent worksheets, which feed from a variety of data worksheet
(all in the same workbook). Am I dreaming? So far, Excel pros that I'v
solicited help from using the sample file referenced above have not been abl
to make this work. Thank you

Did y'all ever find a way around the issue Kathy L had with PT0021? I adapte
the code for my 2 sheet workbook and added a button (as in following code)
However, I also see that when I enable &ldquo;Select Multiple Items&rdquo; i

pagefield and pick a few criteria, the &lsquo;slave&rsquo; pivots do no
follo
the &lsquo;master&rsquo;. Since I&rsquo;m bumbling along here, borrowing cod
a
I can, I&rsquo;ve hit a wall on how to fix that. If you could give me a pus
i
right direction, I&rsquo;d appreciate it. I also posted on the Microsof
Development Network site as a question, and once I&rsquo;ve fixed, I&rsquo;l
put that up too (of course giving Debra et al the original credit)! I&rsquo;
hoping you have already addressed this and it won&rsquo;t inconvenience yo
further to pass on to me. Best Regards, David Shugar
------------------
Private Sub CommandButton1_Click(

On Error Resume Nex
Dim ws As Workshee
Dim wsMain As Workshee
Dim ptMain As PivotTabl
Dim pt As PivotTabl
Dim pfMain As PivotFiel
Dim pi As PivotIte
Dim pf As PivotFiel

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

Application.EnableEvents = Fals
Application.ScreenUpdating = Fals

For Each pfMain In ptMain.PageField
If ws.Name <> wsMain.Name The
For Each pt In ws.PivotTable
pt.RefreshTabl
For Each pf In pt.PageField
If pf.Name = pfMain.Name The
If pfMain.CurrentPage = "(All)" The
pf.CurrentPage = "(All)
Exit Fo
End I
For Each pi In pf.PivotItem
If pi.Name = pfMain.CurrentPage The
pf.CurrentPage = pi.Nam
Exit Fo
End I
Next p
End I
Next p
Next p
End I
Next pfMai

Application.EnableEvents = Tru
Application.ScreenUpdating = Tru

End Sub