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

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" <pmartin1960@hotmail.com> wrote in message
news:324f58a4.0503151532.43bd46c3@posting.google.com...
> 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

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" <ken.wright@NOSPAMntlworld.com>
news:<eI35FzbKFHA.3484@TK2MSFTNGP12.phx.gbl...
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" <pmartin1960@hotmail.com> wrote in message
news:324f58a4.0503151532.43bd46c3@posting.google.com...

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

Hi, Paul Martin

Please kindly advise if I am not suppose to continue in this forum.

I am trying to create VBA codes to loop through a pivot field. Could you help me, please?
I have created a macro as below but the employees name changes every month. So, how can I create a code to loop through the pivot field without needing to change the code.

With ActiveSheet.PivotTables("PivotTable4").PivotFields("Name")
.PivotItems("Bonita Hanwright").Visible = False
.PivotItems("Brian Hulstede").Visible = True
End With
With ActiveSheet.PivotTables("PivotTable5").PivotFields("Name")
.PivotItems("Bonita Hanwright").Visible = False
.PivotItems("Brian Hulstede").Visible = True
End With
With ActiveSheet.PivotTables("PivotTable6").PivotFields("Name")
.PivotItems("Bonita Hanwright").Visible = False
.PivotItems("Brian Hulstede").Visible = True
End With