Loop though all data in PivotItems in a Pivot Table
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" wrote in message
...
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
|