Macro - Pivot Table - Select Items

[Guest]

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?

 

[Tom Ogilvy]

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