Loop though all data in PivotItems in a Pivot Table

[Huyeote]

Hi, all, I have a pivot table which has a column field list all country
names. Every time, I need manuly select one country name in column field and
copy the whole table after change to one seperate worksheet. I need repeat
doing this for all available country name. I tried to make a VBA program and
automatically get all individual country's data. The problem is I don't know
how to loop though all data in PivotItems and select only one each time. I
tried using Macro Recorder to get a hint. I found instead of deselect all
items first and make one desired one visible, the Recorder just make all but
one's Visible property False.
With ActiveSheet.PivotTables("Test").PivotFields("Out Reciever")
.PivotItems("NZ").Visible = False
.PivotItems("GB").Visible = False
.PivotItems("HK").Visible = False
.PivotItems("UK").Visible = False
.PivotItems("US").Visible = False
End With

Can anybody give me a hint how to copy changed pivot table for each data in
the column field to a seperate sheet? Thanks

Regards,

Huyeote

 

[Tom Ogilvy]

Not sure I totally understand, but can't you change your Country field to a
page field and then you can just look at each country separately. (copy
the sheet as necessary and select a different country in the pagefield on
each sheet). A quick way to do this is to put the country field as a
pagefile, then right click on it and select showpages. It will create a
separate page for each country and reproduce the table with that country
selected in the page field.


Regards,
Tom Ogilvy

 

[Huyeote]

Thanks for your reply. I can change the country field to a page field. How
can I write code to copy data of the pivot table for each data in page field
to one seperate worksheet. Meaning copy the table of country1 to worksheet1,
table of country2 to sheet2, and table of country3 to sheet3, etc in a VBA
code. Any hint?

Regards,

Rick Zhu

 

[keepitcool]

MS has done it for you...

(it's NOT in the right click popup menu..)
but it IS on the Pivot toolbar

make sure the Pivot Toolbar is visible.
on the Pivot Toolbar.
goto the PivotTable Menu...
the LAST item in that menu should say.. "Show Pages"

that's the one you want...



keepITcool

< email : keepitcool chello nl (with @ and .) >
< homepage: http://members.chello.nl/keepitcool >