Setting the (All) Page Field 1

[par60056]

I do some pretty strange things formatting Excel pivot tables for clients but I've run into a stumper.

I am trying to loop through all of the values for the page fields of a pivot table and then copy values and formats onto a seperate worksheet for each set of page values.

It works fine except for 1 minor problem. I can't seem to find a way from VBA to reset the page field to the "(All)" value. I can get to all the rest of the values including "(Blank)".

I know I could rewrite the order of the processing to do "(All)" first before the pivot table pages are changed. I just don't like when there should be a way to do something and I can't find it.

Any help would be appriciated.

Peter Richardson

 

[BrainB]

Works OK for me (XL97) :-

ActiveSheet.PivotTables("PivotTable1").PivotFields("Region").CurrentPage = "(All)"

Regards
BrianB
** Let us know if you get something that works !
================================

 

[par60056]

That works but I am trying to index through all of the values. I tried using

with .PivotFields("District&quot
For Each dname In .PivotItems
.CurrentPage = dname.Name
next
end with

and

with .PivotFields("District&quot
For currdist = 1 To .PivotItems.Count
.CurrentPage = .PivotItems(currdist).Name
next
end with

Both methods index through all of the values EXCEPT "(All)".
The hack that I did was to do the following unsatisfying thing.

with .PivotFields("District&quot
For currdist = 0 To .PivotItems.Count
if (currdist = 0) then
.CurrentPage = "(All)"
else
.CurrentPage = .PivotItems(currdist).Name
end if
next
end with

Peter Richardson