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