+ Reply to Thread
Results 1 to 2 of 2

Macro - Pivot Table - Select Items

  1. #1
    S Sainsbury
    Guest

    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. #2
    Tom Ogilvy
    Guest

    Re: 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" <[email protected]> wrote in message
    news:[email protected]...
    > 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 to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1