VBA to select multiple items in pivot table field list

Microsoft Excel 2010 - complete product...
July 21, 2016 at 05:40:23
Specs: Windows 7
Hi, I have a workbook with one main tab and 16 other tabs (mainly charts that are derived from pivots). The source of all pivots is the main tab.

In the main tab, one of the fields is a “Status” field that changes almost daily. "Status" is one of the pivottable field list. I use "select multiple items" within this field list to select the Statuses relevant to the metrics I want.

I want to create a macro that would look at the options under the Status field list and select the items I want if they are visible.

I have the following macro so far, but the problem is if one of these statuses is not active and thus is not in the main tab the macro runs into a bug because it can’t find it among the options available under Status in the field list.

I believe the solution would be a macro based on “if/then” that says if this reporting filter exists then select it in the report filters options.
I would appreciate the help.

With ActiveSheet.PivotTables("PivotTable1").PivotFields(" Status")
.PivotItems("Phase 1 - Review+").Visible = True
.PivotItems("Phase 1 - CWG+").Visible = True
.PivotItems("Phase 5 - PIAR Review+").Visible = True
.PivotItems("Phase 2 - Kickoff+").Visible = True
.PivotItems("Phase 3+").Visible = True
.PivotItems("Phase 4+").Visible = True
.PivotItems("Phase 5+").Visible = True
.PivotItems("Phase 5 - Implementation+").Visible = True

See More: VBA to select multiple items in pivot table field list

Reply ↓  Report •

July 21, 2016 at 06:16:21
Unfortunately, I have not coded for (or even used) Pivot Tables, so I can't be much help here. Maybe someone else will step up with some assistance.

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.

Reply ↓  Report •

July 22, 2016 at 08:14:45
Thanks for checking! Seems no one is interested unfortunately.

Reply ↓  Report •

Related Solutions

Ask Question