Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
need error loop in pivot table routine | Excel Discussion (Misc queries) | |||
Filter lines containing pivot table and non pivot table data | Excel Worksheet Functions | |||
Pivot Change PivotItems - Visible/Hide with VBA | Excel Worksheet Functions | |||
For Each Loop with Pivot Table | Excel Discussion (Misc queries) | |||
Add PivotItems to PivotTable / Enabling - Disabling PivotItems | Excel Programming |