Loop through PivotItems of PageField - including "(All)"
 

Hi all

I know how to loop through the PivotItems of a PageField on a
PivotTable. I want to extract data into multiple tables, one for each
pivot item. The following line does this well:

For Each pi In pt.PageFields("Staff Name").PivotItems

I am building multiple tables, one for each staff name. But I also
want to include a table for "(All)". Is there an easy way to include
this, preferably within a loop, so that I don't have to repeat the
code to build one table?

Thanks in advance

Paul Martin
Melbourne, Australia

Loop through PivotItems of PageField - including "(All)"
 

Drag your Staff name field into the Page items area and then use the Show
pages option

--
Regards
Ken....................... Microsoft MVP - Excel
Sys Spec - Win XP Pro / XL 97/00/02/03

----------------------------------------------------------------------------
It's easier to beg forgiveness than ask permission :-)
----------------------------------------------------------------------------

"Paul Martin" wrote in message
om...
Hi all

I know how to loop through the PivotItems of a PageField on a
PivotTable. I want to extract data into multiple tables, one for each
pivot item. The following line does this well:

For Each pi In pt.PageFields("Staff Name").PivotItems

I am building multiple tables, one for each staff name. But I also
want to include a table for "(All)". Is there an easy way to include
this, preferably within a loop, so that I don't have to repeat the
code to build one table?

Thanks in advance

Paul Martin
Melbourne, Australia

Loop through PivotItems of PageField - including "(All)"
 

Hi Ken

Thanks for the reply, but it's not what I'm looking for. Show Pages
creates one sheet for each pivot item. What I've done is create a
pivot table on ONE sheet, and built various tables on the SAME sheet
based on the pivot table.

I am looping through each pivot item to build one table at a time.
But looping through the pivot items does not enable me to include
"(All)" as part of the loop.

I suppose I could achieve this by creating an array with "All" as the
first element and then adding each pivot item to the array. Any other
ideas?

Paul Martin
Melbourne, Australia

--------------------------------------------------------------

"Ken Wright"
...
Drag your Staff name field into the Page items area and then use the
Show pages option

--
Regards
Ken....................... Microsoft MVP - Excel
Sys Spec - Win XP Pro / XL 97/00/02/03

----------------------------------------------------------------------------
It's easier to beg forgiveness than ask permission
:-)
----------------------------------------------------------------------------

"Paul Martin" wrote in message
om...

Hi all

I know how to loop through the PivotItems of a PageField on a
PivotTable. I want to extract data into multiple tables, one for each
pivot item. The following line does this well:

For Each pi In pt.PageFields("Staff Name").PivotItems

I am building multiple tables, one for each staff name. But I also
want to include a table for "(All)". Is there an easy way to include
this, preferably within a loop, so that I don't have to repeat the
code to build one table?

Thanks in advance

Paul Martin
Melbourne, Australia