Prev: Does a formula exist that will lookup on ALL tabs, not just on
Next: Date Formula
From: Kathy L. on 9 Apr 2010 18:22
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.
From: Roger Govier on 10 Apr 2010 04:38
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.

--
Regards
Roger Govier

Kathy L. wrote:
> 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.
From: Herbert Seidenberg on 10 Apr 2010 14:02
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.
From: Roger Govier on 10 Apr 2010 16:28
Hi Herbert

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

--
Regards
Roger Govier

Herbert Seidenberg wrote:
> 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.
From: Herbert Seidenberg on 10 Apr 2010 17:43
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
 |  Next  |  Last
Pages: 1 2
Prev: Does a formula exist that will lookup on ALL tabs, not just on
Next: Date Formula