Search code examples
excelvbafilterpivotpivot-table

Macro to change the Pivot filter automatically does not work

I have a report with several pivot tables that run off production data, orders and deliveries. I run it daily and then change the filters in my report to select all of the delivery numbers, and then unselect the blank delivery numbers.

In other words, I'm only selecting those orders that contain a delivery number. If the delivery number is blank, then the order is not set to ship yet.

    ActiveSheet.PivotTables("PivotTable5").PivotFields("Del #").CurrentPage = _
        "(All)"
    With ActiveSheet.PivotTables("PivotTable5").PivotFields("Del #")
        .PivotItems("(blank)").Visible = True
    End With
    ActiveSheet.PivotTables("PivotTable5").PivotFields("Del #").CurrentPage = _
        "(All)"
    With ActiveSheet.PivotTables("PivotTable5").PivotFields("Del #")
        .PivotItems("(blank)").Visible = False
    End With
End Sub

I want for this to go in and select all of the delivery numbers, except for the blank delivery numbers.

Screen shot

It seems to fail to select "All", and only has selected what was previously selected the day prior.

Solution

  • How to show every PivotItem except blank ones

    If PivotTables().PivotFields().EnableMultiplePageItems is set to True, then you can not select all PivotItems by setting the PivotFields.CurrentPage to ="(All)" (although the macro recorder records only that!).

    To select all PivotItems, just use PivotField.ClearManualFilter or PivotField.ClearAllFilters (on the PivotField, not accidentially on the PivotTable!). Afterwards you can hide (unselect) the blank ones.

    With WorkSheet.PivotTables(...).PivotFields(...)
    .ClearManualFilter    ' or ClearAllFilters
    If .PivotItems.Count > 1 Then   ' at least 1 has to remain visible
        .PivotItems("(blank)").Visible = False
    End If
End With

    Error Handling
    At least one PivotItem has to remain visible. You'll get an error, if there are only blanks and you try to hide them.
    If there are no blanks either, then you get an error, which you should catch with On Error Resume Next, or you can loop over all items first to check if one of them is called "(blank)". It is not enough to check the last PivotItem's name like If .PivotItems(.PivotItems.Count).Name = "(blank)", as it is not necessarily the last entry.

    Your example should work with this:

    With ActiveSheet.PivotTables("PivotTable5").PivotFields("Del #")
    .ClearManualFilter
    If .PivotItems.Count > 1 Then   ' at least 1 has to remain visible
        .PivotItems("(blank)").Visible = False
    End If
End With

    Maybe you need to ActiveSheet.PivotTables("PivotTable5").RefreshTable every day additionally.

    The other way round: How to show only the blank PivotItems

    If EnableMultiplePageItems = True and one or many were selected, but not the blank ones, then CurrentPage = "(blank)" raises an error. You have to enable the blank ones first, either by clearing the filters like above or by making the blanks visible additionally, and then you can select the page with blank ones only:

    WorkSheet.PivotTables().PivotFields().PivotItems("(blank)").Visible = True
WorkSheet.PivotTables().PivotFields().CurrentPage = "(blank)"