如何自动更新数据透视表在Excel中使用VBA

发表于： 2020年10月22日 2020年11月12日
标签： tips

在本文中，我们将学习如何在Microsoft Excel 2010中使用VBA自动更新数据透视表。

为什么我们需要更新数据透视表图表？

众所周知，每当对数据透视表的源数据进行更改时，它都不会立即反映在数据透视表中。我们需要刷新数据透视表以查看更改。而且，如果您发送更新的文件而不刷新数据透视表，您可能会感到尴尬。

因此，在本文中，我们将学习如何使用VBA自动刷新数据透视表。

这是自动刷新工作簿中的数据透视表的简单语法。

什么是数据透视缓存？每个数据透视表将数据存储在数据透视缓存中。这就是为什么透视图能够显示以前的数据的原因。刷新数据透视表时，它将使用新的源数据更新缓存以反映数据透视表上的更改。

'Code in Source Data Sheet Object

Private Sub Worksheet_Deactivate()

sheetname_of_pivot_table.PivotTables("pivot_table_name").PivotCache.Refresh

End Sub

因此，我们只需要一个宏即可刷新数据透视表的缓存。我们将使用`link：/ events-in-vba-the-worksheet-events-in-excel-vba [worksheet event]`来执行此操作，这样我们就不必手动运行宏。

在哪里编码以自动刷新数据透视表？如果您的源数据表和数据透视表位于不同的表中，则VBA代码应放在源数据表中。

在这里，我们将使用Worksheet_SelectionChange事件。每当我们从源数据表切换到另一个表时，这将使代码运行。

稍后我将解释为什么使用此事件。

在这里，我在Sheet2中有源数据，在Sheet1中有数据透视表。

使用CTRL + F11键打开VBE。在项目资源管理器中，您可以看到三个对象，Sheet1，Sheet2和工作簿。

由于Sheet2包含源数据，因此双击sheet2对象。

现在，您可以在代码区域的顶部看到两个下拉菜单。从第一个下拉列表中，选择工作表。然后从第二个下拉菜单中选择停用。这将插入一个空的子名称Worksheet_Deactivate。我们的代码将写在此子代码中。一旦用户从该工作表切换到任何其他工作表，该子程序中写入的任何行都将被执行。

在sheet1上，我有两个数据透视表。我只想刷新一个数据透视表。为此，我需要知道数据透视表的名称。要知道任何数据透视表的名称，请在该数据透视表中选择任何单元格转到“数据透视表分析”选项卡。在左侧，您将看到数据透视表的名称。您还可以在此处更改数据透视表的名称。

现在我们知道了数据透视表的名称，我们可以编写一条简单的行来刷新数据透视表。

完成了。

Private Sub Worksheet_Deactivate()

Sheet1.PivotTables("PivotTable1").PivotCache.Refresh

End Sub

现在，无论何时从源数据切换，此vba代码都将运行以刷新数据透视表1。如下面的gif所示。

如何刷新工作簿中的所有数据透视表？在上面的示例中，我们只想刷新一个特定的数据透视表。但是，如果您要刷新工作簿中的所有数据透视表，则只需对代码进行一些更改。

在这段代码中，我们使用“ link：/ vba-for-loops-with-7-examples [For loop]”循环遍历工作簿中的每个数据透视缓存。 ThisWorkbook对象包含所有数据透视表缓存。要访问它们，我们使用ThisWorkbook.PivotCaches。

Private Sub Worksheet_Deactivate()

'Sheet1.PivotTables("PivotTable1").PivotCache.Refresh



For Each pc In ThisWorkbook.PivotCaches

pc.Refresh

Next pc



End Sub

为什么使用Worksheet_Deactivate事件？

如果要在源数据中进行任何更改后立即刷新数据透视表，则应使用Worksheet_Change事件。但我不建议这样做。每当您在工作表中进行任何更改时，它将使您的工作簿运行代码。您可能需要做数百次更改才能看到结果。但是excel会在每次更改时刷新数据透视表。这将浪费处理时间和资源。因此，如果您在不同的工作表中都有数据透视表和数据，则最好使用“工作表停用事件”。它可以让您完成工作。切换到数据透视表以查看更改后，它将修改更改。

如果您在同一工作表上有数据透视表和源数据，并且希望数据透视表自行自动刷新，则可能需要在以下情况下使用`link：/ tips-using-worksheet-change-event-run-macro-when-进行任何更改[Worksheet_Change Event]`。

在源数据中进行更改后，如何刷新工作簿中的所有内容？如果要刷新工作簿上的所有内容（图表，数据透视表，公式等），则可以使用ThisWorkbook.RefreshAll命令。

Private Sub Worksheet_Change(ByVal Target As Range)

Sheet1.PivotTables("PivotTable1").PivotCache.Refresh

End Sub

请注意，此代码不会更改数据源。因此，如果您在源数据下方添加数据，则此代码不会自动包含该数据。您可以使用“链接：/ table-excel-2007-17-amazing-features-of-excel-tables [Excel表格]”来存储源数据。如果您不想使用表，我们也可以使用VBA包括新数据。我们将在下一个教程中学习它。

Private Sub Worksheet_Change(ByVal Target As Range)

ThisWorkbook.RefreshAll

End Sub

希望本文有关如何在Microsoft Excel中使用VBA自动更新数据透视表进行说明。在此处查找有关计算值和相关Excel公式的更多文章。如果您喜欢我们的博客，请在Facebook上与您的朋友分享。您也可以在Twitter和Facebook上关注我们。我们希望收到您的来信，请让我们知道我们如何改进，补充或创新我们的工作，并为您做得更好。写信给我们[email protected]。

link：/ excel-pivot-tables-如何动态更新-excel-数据透视表数据源范围[如何在Excel中动态更新数据透视表数据源范围]：要动态更改数据透视表的源数据范围，我们使用数据透视表缓存。在VBA中，使用数据透视表对象，如下所示…

`link：/ events-in-vba-run-macro-if-any-change-make-on-sheet-range [如果在指定范围内对表进行了任何更改，则运行宏]：在您的VBA实践中，您会得到当某个范围或单元格发生变化时，需要运行宏。在这种情况下，要在对目标范围进行更改时运行宏，请使用change事件。

`link：/ tips-using-worksheet-change-event-to-run-macro-when-any-change-made- [在表格上进行任何更改时运行宏]：因此，每当表格出现时就运行宏更新，我们使用VBA的工作表事件。

最简单的VBA代码突出显示当前使用的行和列：使用此小VBA代码段突出显示工作表的当前行和列。

`link：/ events-in-vba-the-worksheet-events-in-excel-vba [Excel VBA中的工作表事件]：当您希望宏在指定事件发生时运行时，工作表事件非常有用工作表。