Filtering a Pivot table via macro code

Topic

New Reply

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.

Reply | Quote

Replies

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

Reply | Quote

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

Reply | Quote