自动更改枢轴过滤器的宏不起作用 [英] 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.

似乎无法选择全部"，并且只选择了前一天之前选择的内容.

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

推荐答案

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

如果 PivotTables().PivotFields().EnableMultiplePageItems 设置为 True，那么您不能通过设置 all PivotItemscode>PivotFields.CurrentPage 到 =(All)"(虽然宏记录器只记录那个！).

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!).

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

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

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

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.

你的例子应该适用于这个:

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.

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

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屋！