Solved Creating an excel macro that selects filters in pivots

July 19, 2016 at 05:46:57
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. This field is one of the main filters in my pivots for the charts.

I want to create a macro that would look at the filters in the pivots and mark all the statuses that are there. 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 that particular field.

I need something like an “if command” that says if this status exists then mark the existing filter in pivot field.

I would appreciate the help.

See More: Creating an excel macro that selects filters in pivots

Reply ↓  Report •

July 19, 2016 at 05:50:15
✔ Best Answer
Here's the sample of the current macro I have:

 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

As I said, since the statuses change, it is often the case that there is no Phase 3+ on the main tab, and this causes an error.

message edited by AngieP

Reply ↓  Report •
Related Solutions

Ask Question