Results 1 to 3 of 3
  1. #1
    Star Lounger
    Join Date
    Feb 2009
    Location
    Kings Park, Victoria, Australia
    Posts
    90
    Thanks
    15
    Thanked 5 Times in 5 Posts

    Question Filtering a Pivot table via macro code

    Hi,

    I have a macro that creates 2 pivot tables, both tables have some filtering set as follows, however, the values that I filter on do not always have any data.

    With ActiveSheet.PivotTables("PivotTable1").PivotFields ("Underwriter")
    .PivotItems("CP_FI BOM_Renewals").Visible = False
    .PivotItems("GSSC_Processing").Visible = False

    My question is, that if the field does not contain either or both of the values, how do I stop the code from executing? If I leave it as is, and then one &/or both of the field values don't exist then the macro stops in debug mode and I have to change the line focus and continue with CTRL-F9 & F5. As I am going on annual leave shortly, I would prefer the macro to not go into debug mode to make it easier for whoever is running the code in my absence.
    Maria
    Simmo7
    Victoria, Australia

  2. #2
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts
    You could tell VBA to ignore the errors of the Pivot items and turn the error handling back on when they are all finished.

    With ActiveSheet.PivotTables("PivotTable1").PivotFields ("Underwriter")
    On error resume next
    .PivotItems("CP_FI BOM_Renewals").Visible = False
    .PivotItems("GSSC_Processing").Visible = False
    on error goto 0


    [An alternate way I have found to limit a list that does not require coding is to create a display (or a don't display, whichever is smaller and easier to maintain) list somewhere in the workbook. Then create a column in the data for the pivot table that does a match on the appropriate pivottable data, with that list and check the results with ISNUMBER. If in the list the isnumber will be true, if not found the value will give a false. If you put this field as the pivotfield, you can select true or false to get the items that are or are not in the list. To alter the items the display list only has to be edited and the pivot table refreshed. No macro is required. ]

    Steve

  3. The Following User Says Thank You to sdckapr For This Useful Post:

    simmo7 (2014-10-16)

  4. #3
    Star Lounger
    Join Date
    Feb 2009
    Location
    Kings Park, Victoria, Australia
    Posts
    90
    Thanks
    15
    Thanked 5 Times in 5 Posts
    Thanks Steve, I just tested with the error handling and it worked, no debug mode triggered. Just what I wanted. I am not sure if your alternate method would work as the 2 pivot tables are created by the macro they do not already exist. I import data on a weekly basis from our mainframe to a new worksheet and format as needed then run the addPivotTables macro.

    Regards,
    Maria
    Maria
    Simmo7
    Victoria, Australia

Posting Permissions

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