vba Excel数据透视表自动更新后如何自动刷新Excel自动过滤结果
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/41856894/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
How to auto refresh Excel auto-filter results after Excel PivotTable auto update
提问by David Robinson
I have created a "Summary" tab using an Excel Pivot Table.
我使用 Excel 数据透视表创建了一个“摘要”选项卡。
The Pivot Table shows the sums of selected values in various categories stored in a "Details" tab.
数据透视表显示存储在“详细信息”选项卡中的各种类别中选定值的总和。
The Details tab contains...
详细信息选项卡包含...
Select? Type Price
Y Shoe .25
N Shoe .50
Y Boot .00
N Clog .00
Y Shoe Select? Y
Row Labels Sum of Price
Boot .00
Shoe .75
.50
The Summary tab shows a Pivot Table like this...
“摘要”选项卡显示了这样的数据透视表...
Private Sub Worksheet_Activate()
Sheets("Summary").PivotTables("PivotTable1").RefreshTable
End Sub
Whenever something is changed in the Details tab, and someone switches to the Summary tab, I want the Pivot Table to auto update to match the new data.
每当“详细信息”选项卡中的某些内容发生更改，并且有人切换到“摘要”选项卡时，我希望数据透视表自动更新以匹配新数据。
This is easy if just the values change (using the first code snippet below), but if the Y and N change, the filtering does not update. Anything that has changed from N to Y is not shown until the filtering is manually updated.
如果只是值发生变化（使用下面的第一个代码片段），这很容易，但如果 Y 和 N 发生变化，则过滤不会更新。在手动更新过滤之前，不会显示从 N 更改为 Y 的任何内容。
This is what I have so far...
这是我到目前为止...
The code to auto-update a Pivot Table is e.g. ...
自动更新数据透视表的代码是例如...
Private Sub Worksheet_Change(ByVal Target As Range)
With ActiveWorkbook.Worksheets("Details").ListObjects("Table1")
.AutoFilter.ApplyFilter
End With
End Sub
The code to auto-refresh an Excel auto-filter column in a table (that's nota Pivot Table) is e.g. ...
自动刷新表（不是数据透视表）中的 Excel 自动筛选列的代码是例如...
Worksheets("Details").PivotTables("PivotTable1") _
.PageRange.Interior.Color = vbYellow
...but I cannot figure out how to apply AutoFilter.ApplyFilter to a Pivot Table. Any ideas? Or Equivalent?
...但我无法弄清楚如何将 AutoFilter.ApplyFilter 应用于数据透视表。有任何想法吗？或同等学历？
The nearest thing I found to touch the Pivot Table filter is this code which will turn the part I want to update yellow...
我发现最接近数据透视表过滤器的是这段代码，它将把我想要更新的部分变成黄色......
Private Sub Worksheet_Activate()
Worksheet_Change 'Macro name
End Sub
...but I don't want to turn it yellow :-) , I want to re-apply the filter to the modified data so I see what I shouldsee, rather than the wrong values and missing rows.
...但我不想把它变成黄色 :-) ，我想将过滤器重新应用于修改后的数据，以便我看到我应该看到的内容，而不是错误的值和丢失的行。
Many thanks.
非常感谢。
回答by Cyril
Two things I can think of... running a macro on activation or deactivation of a sheet.
我能想到的两件事......在工作表的激活或停用时运行宏。
Private Sub Worksheet_Activate()
ActiveSheet.PivotTables("PivotTable1").RefreshTable
On Error Resume Next
ActiveSheet.PivotTables("PivotTable1").PivotFields("Selection") _
.CurrentPage = "Y"
End Sub
This would be tied specifically to the sheet for your summary. Similarly, you would use Worksheet_Deactivate() tied to your other sheet. You can also throw in the pivot table update to this same macro.
这将专门与您的摘要表相关联。同样，您将使用 Worksheet_Deactivate() 绑定到您的其他工作表。您还可以将数据透视表更新添加到同一宏中。
回答by David Robinson
The result of recording the macro (not updating the filter, but re-applying the choice of filtering by "Y") and editing it in is...
记录宏（不是更新过滤器，而是重新应用“Y”过滤的选择）并编辑它的结果是......
##代码##It works for me. I had to add the On Error to (clumsily) catch the case where there was no "Y" to filter by.
这个对我有用。我不得不将 On Error 添加到（笨拙地）捕捉没有“Y”可供过滤的情况。
If anyone needs a more generic solution to refresh the filtering without re-applying a specific filter choice, the above will not work, but that's no problem in my use case.
如果有人需要更通用的解决方案来刷新过滤而不重新应用特定的过滤器选择，则上述方法将不起作用，但这在我的用例中没有问题。
Many thanks.
非常感谢。
