自动更改数据透视滤镜的宏不起作用 [英] Macro to change the Pivot filter automatically does not work

问题描述

我有一个包含几个数据透视表的报表，这些数据透视表用于生产数据，订单和交货.我每天运行一次，然后在报告中更改过滤器以选择所有交货编号，然后取消选择空白交货编号.

换句话说，我只选择那些包含交货编号的订单.如果交货号为空，则该订单尚未设置为装运.

    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

我要输入所有的交货号，空白的交货号除外.

似乎无法选择全部"，仅选择了前一天选择的内容.

解决方案

如何显示除空白项之外的每个PivotItem

如果将PivotTables().PivotFields().EnableMultiplePageItems设置为True，则无法通过将PivotFields.CurrentPage设置为="(All)"来选择 all PivotItems(尽管宏记录器仅记录该信息！). /p>

要选择所有PivotItem，只需使用PivotField.ClearManualFilter或PivotField.ClearAllFilters(在PivotField上，而不是偶然在PivotTable上！).之后，您可以隐藏(取消选择)空白的内容.

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

错误处理
至少一个PivotItem必须保持可见.如果只有 个空格，并且尝试隐藏它们，则会出现错误.
如果没有没有空格，那么您会得到一个错误，应该使用On Error Resume Next捕获该错误，或​​者您可以首先遍历所有项以检查其中一项是否被称为(空白)". .仅检查最后一个PivotItem的名称(如If .PivotItems(.PivotItems.Count).Name = "(blank)")是不够的，因为它不一定是最后一个条目.

您的示例应与此兼容:

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

也许您每天还需要ActiveSheet.PivotTables("PivotTable5").RefreshTable.

反之:如何仅显示空白的PivotItems

如果选择了EnableMultiplePageItems = True并且选择一个或多个，但不是空白，则CurrentPage = "(blank)"会引发错误. 您必须先启用空白的，要么清除上面的过滤器，要么使空白另外可见，然后选择仅包含空白的页面:

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

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.

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

解决方案

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)"

这篇关于自动更改数据透视滤镜的宏不起作用的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！