Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Macro - Pivot Table - Select Items
I need some help with a macro script.
I have a pivot table and I need to only show specific data by selecting one item at a time from a pivot table coloumn called "CustRegDirector". Once the item is selected I need to copy all data on the sheet and paste it into a new file, save and close the file and then go back to the original spreadsheet and then repeat the whole process for each item in the list. The above is simple enough if the items in the list stay the same however they dont! This is my problem, how to tell the script to select items which may not yet exist? Basically the script needs to go through each item in the list one by one from start to finish. Example of code I am using is below, this example only selects and copies one item..... Sub NewFile2() ' ' NewFile2 Macro ' Macro recorded 18/11/2005 by sainsburys ' ' Keyboard Shortcut: Ctrl+w ' With ActiveSheet.PivotTables("PivotTable3").PivotFields ("CustRegDirector") .PivotItems("Alex Rampton").Visible = False .PivotItems("Andrew Hart").Visible = False .PivotItems("Andrew Popple").Visible = False .PivotItems("Andy Clarke").Visible = False .PivotItems("Andy Hart").Visible = False .PivotItems("Bob Kirkland").Visible = False .PivotItems("Chris Larkin").Visible = False .PivotItems("Colin Clement").Visible = False .PivotItems("Dale Sandison").Visible = False .PivotItems("Dave Stewart").Visible = False .PivotItems("David Gardener").Visible = False .PivotItems("David Gardner").Visible = False .PivotItems("David Kelman").Visible = False .PivotItems("Greig Perrers").Visible = False .PivotItems("Iain Paul").Visible = False .PivotItems("Ian Smith").Visible = False .PivotItems("John Gallantry").Visible = False .PivotItems("John Gibson").Visible = False .PivotItems("John Warby").Visible = False .PivotItems("Kevin Woodcock").Visible = False .PivotItems("Marc Chitryn").Visible = False .PivotItems("Mark Daly").Visible = False .PivotItems("Mark Walker").Visible = False .PivotItems("Martin Beale").Visible = False .PivotItems("Martin Sizeland").Visible = False .PivotItems("Mike Heathman").Visible = False .PivotItems("Nigel Fossey").Visible = False .PivotItems("Norman Watson").Visible = False .PivotItems("Paul Jones").Visible = False .PivotItems("Paul Massenhove").Visible = False .PivotItems("Paul Mcgill").Visible = False End With With ActiveSheet.PivotTables("PivotTable3").PivotFields ("CustRegDirector") .PivotItems("Peter Brewer").Visible = False .PivotItems("Peter Thompson").Visible = False .PivotItems("Phil Gransden").Visible = False .PivotItems("Richard Adams").Visible = False .PivotItems("Simon Denton").Visible = False .PivotItems("Simon Garrett").Visible = False .PivotItems("Stephen Boyle").Visible = False .PivotItems("Steve Boyle").Visible = False .PivotItems("Steve Quarrington").Visible = False .PivotItems("Tony Crowther").Visible = False .PivotItems("Tpph Regional Direct").Visible = False .PivotItems("Trevor Williams").Visible = False .PivotItems("(blank)").Visible = False End With Rows("4:23").Select Selection.Copy Workbooks.Add ActiveSheet.Paste Application.CutCopyMode = False ActiveWorkbook.SaveAs Filename:= _ "NEW FILENAME", FileFormat:= _ xlNormal, Password:="", WriteResPassword:="", ReadOnlyRecommended:=False _ , CreateBackup:=False ActiveWindow.Close End Sub I have read through other posts and been to the http://www.contextures.com for tips but I can not see anything that relates to what I am trying to do, most people seem to want to select items that already exist, select all items etc. Can anyone help? |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Macro - Pivot Table - Select Items
Dim pf as PivotField, pvtItm as PivotItem, pvtItm1 as PivotItem
set pf = ActiveSheet.PivotTables("PivotTable3").PivotFields ("CustRegDirector") for each pvtItm in pf pvtItm.Visible = true for each pvtItm1 in pf if pvtItm1.Value < pvtItm.Value then pvtItm1.Visible = False end if Next ' now copy your data Next -- Regards, Tom Ogilvy "S Sainsbury" wrote in message ... I need some help with a macro script. I have a pivot table and I need to only show specific data by selecting one item at a time from a pivot table coloumn called "CustRegDirector". Once the item is selected I need to copy all data on the sheet and paste it into a new file, save and close the file and then go back to the original spreadsheet and then repeat the whole process for each item in the list. The above is simple enough if the items in the list stay the same however they dont! This is my problem, how to tell the script to select items which may not yet exist? Basically the script needs to go through each item in the list one by one from start to finish. Example of code I am using is below, this example only selects and copies one item..... Sub NewFile2() ' ' NewFile2 Macro ' Macro recorded 18/11/2005 by sainsburys ' ' Keyboard Shortcut: Ctrl+w ' With ActiveSheet.PivotTables("PivotTable3").PivotFields ("CustRegDirector") .PivotItems("Alex Rampton").Visible = False .PivotItems("Andrew Hart").Visible = False .PivotItems("Andrew Popple").Visible = False .PivotItems("Andy Clarke").Visible = False .PivotItems("Andy Hart").Visible = False .PivotItems("Bob Kirkland").Visible = False .PivotItems("Chris Larkin").Visible = False .PivotItems("Colin Clement").Visible = False .PivotItems("Dale Sandison").Visible = False .PivotItems("Dave Stewart").Visible = False .PivotItems("David Gardener").Visible = False .PivotItems("David Gardner").Visible = False .PivotItems("David Kelman").Visible = False .PivotItems("Greig Perrers").Visible = False .PivotItems("Iain Paul").Visible = False .PivotItems("Ian Smith").Visible = False .PivotItems("John Gallantry").Visible = False .PivotItems("John Gibson").Visible = False .PivotItems("John Warby").Visible = False .PivotItems("Kevin Woodcock").Visible = False .PivotItems("Marc Chitryn").Visible = False .PivotItems("Mark Daly").Visible = False .PivotItems("Mark Walker").Visible = False .PivotItems("Martin Beale").Visible = False .PivotItems("Martin Sizeland").Visible = False .PivotItems("Mike Heathman").Visible = False .PivotItems("Nigel Fossey").Visible = False .PivotItems("Norman Watson").Visible = False .PivotItems("Paul Jones").Visible = False .PivotItems("Paul Massenhove").Visible = False .PivotItems("Paul Mcgill").Visible = False End With With ActiveSheet.PivotTables("PivotTable3").PivotFields ("CustRegDirector") .PivotItems("Peter Brewer").Visible = False .PivotItems("Peter Thompson").Visible = False .PivotItems("Phil Gransden").Visible = False .PivotItems("Richard Adams").Visible = False .PivotItems("Simon Denton").Visible = False .PivotItems("Simon Garrett").Visible = False .PivotItems("Stephen Boyle").Visible = False .PivotItems("Steve Boyle").Visible = False .PivotItems("Steve Quarrington").Visible = False .PivotItems("Tony Crowther").Visible = False .PivotItems("Tpph Regional Direct").Visible = False .PivotItems("Trevor Williams").Visible = False .PivotItems("(blank)").Visible = False End With Rows("4:23").Select Selection.Copy Workbooks.Add ActiveSheet.Paste Application.CutCopyMode = False ActiveWorkbook.SaveAs Filename:= _ "NEW FILENAME", FileFormat:= _ xlNormal, Password:="", WriteResPassword:="", ReadOnlyRecommended:=False _ , CreateBackup:=False ActiveWindow.Close End Sub I have read through other posts and been to the http://www.contextures.com for tips but I can not see anything that relates to what I am trying to do, most people seem to want to select items that already exist, select all items etc. Can anyone help? |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
How do I select multiple items in an Excel 2003 Pivot table | Excel Discussion (Misc queries) | |||
Select multipul 'Page' items in a Pivot Table | Charts and Charting in Excel | |||
Disable "Select Multiple Items" in Pivot Table | Excel Programming | |||
select multiple items in olap pivot table | Excel Programming | |||
Macro to Refresh Pivot Table AND Delete Old Items | Excel Programming |