Loop though all data in PivotItems in a Pivot Table
 

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

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

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

Loop though all data in PivotItems in a Pivot Table
 

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


"Huyeote" wrote:

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