blocks|key|1548217|text|是。|type|unstyled|depth|inlineStyleRanges|entityRanges|data|1548218|ThisWorkbook.RefreshAll|code-block|syntax|javascript|1548219|或者，如果您的Excel版本足够旧，|1548220|Dim+Sheet+as+WorkSheet,+Pivot+as+PivotTable
For+Each+Sheet+in+ThisWorkbook.WorkSheets
++++For+Each+Pivot+in+Sheet.PivotTables
++++++++Pivot.RefreshTable
++++++++Pivot.Update
++++Next
Next|1548221|entityMap^0|0|0|0|0^^$0|@$1|2|3|4|5|6|7|M|8|@]|9|@]|A|$]]|$1|B|3|C|5|D|7|N|8|@]|9|@]|A|$E|F]]|$1|G|3|H|5|6|7|O|8|@]|9|@]|A|$]]|$1|I|3|J|5|D|7|P|8|@]|9|@]|A|$E|F]]|$1|K|3|-4|5|6|7|Q|8|@]|9|@]|A|$]]]|L|$]]

Yes.

<pre><code>ThisWorkbook.RefreshAll
</code></pre>

Or, if your Excel version is old enough,

<pre><code>Dim Sheet as WorkSheet, Pivot as PivotTable
For Each Sheet in ThisWorkbook.WorkSheets
 For Each Pivot in Sheet.PivotTables
 Pivot.RefreshTable
 Pivot.Update
 Next
Next
</code></pre>

blocks|key|1548258|text|透视表工具栏中有一个“全部刷新”选项。我吃饱了。不需要做任何其他的事情。|type|unstyled|depth|inlineStyleRanges|entityRanges|data|1548259|按ctrl%2Balt%2BF5|1548260|entityMap^0|0|0^^$0|@$1|2|3|4|5|6|7|F|8|@]|9|@]|A|$]]|$1|B|3|C|5|6|7|G|8|@]|9|@]|A|$]]|$1|D|3|-4|5|6|7|H|8|@]|9|@]|A|$]]]|E|$]]

There is a refresh all option in the Pivot Table tool bar. That is enough. Dont have to do anything else.

Press ctrl+alt+F5

blocks|key|1548304|text|在某些情况下，您可能希望区分PivotTable和它的PivotCache。缓存有它自己的刷新方法和自己的集合。所以我们可以刷新所有的PivotCaches而不是PivotTables。|type|unstyled|depth|inlineStyleRanges|entityRanges|data|1548305|有什么不同？当您创建新的透视表时，系统会询问您是否要基于以前的表创建透视表。如果您选择“否”，此数据透视表将获得自己的缓存，并将源数据的大小加倍。如果你说“是”，你会保持你的WorkBook很小，但是你会添加到一个共享单个缓存的透视表集合中。当您刷新该集合中的任何单个透视表时，整个集合都会被刷新。因此，您可以想象刷新WorkBook中的每个缓存与刷新WorkBook中的每个数据透视表之间的区别。|1548306|entityMap^0|0|0^^$0|@$1|2|3|4|5|6|7|F|8|@]|9|@]|A|$]]|$1|B|3|C|5|6|7|G|8|@]|9|@]|A|$]]|$1|D|3|-4|5|6|7|H|8|@]|9|@]|A|$]]]|E|$]]

In certain circumstances you might want to differentiate between a PivotTable and its PivotCache. The Cache has it's own refresh method and its own collections. So we could have refreshed all the PivotCaches instead of the PivotTables.

The difference? When you create a new Pivot Table you are asked if you want it based on a previous table. If you say no, this Pivot Table gets its own cache and doubles the size of the source data. If you say yes, you keep your WorkBook small, but you add to a collection of Pivot Tables that share a single cache. The entire collection gets refreshed when you refresh any single Pivot Table in that collection. You can imagine therefore what the difference might be between refreshing every cache in the WorkBook, compared to refreshing every Pivot Table in the WorkBook.

blocks|key|1763274|text|如果您使用的是MS+Excel+2003，请转到“查看”->“工具栏”->“透视表”。在此工具栏中，我们可以通过单击“刷新”来进行刷新。这个符号。|type|unstyled|depth|inlineStyleRanges|entityRanges|data|1763275|entityMap^0|0^^$0|@$1|2|3|4|5|6|7|D|8|@]|9|@]|A|$]]|$1|B|3|-4|5|6|7|E|8|@]|9|@]|A|$]]]|C|$]]

If you are using MS Excel 2003 then go to view->Tool bar->Pivot Table From this tool bar we can do refresh by clicking ! this symbol.

blocks|key|1548406|text|我在最近使用了下面列出的命令，它似乎工作得很好。|type|unstyled|depth|inlineStyleRanges|entityRanges|data|1548407|ActiveWorkbook.RefreshAll|code-block|syntax|javascript|1548408|希望这能有所帮助。|1548409|entityMap^0|0|0|0^^$0|@$1|2|3|4|5|6|7|K|8|@]|9|@]|A|$]]|$1|B|3|C|5|D|7|L|8|@]|9|@]|A|$E|F]]|$1|G|3|H|5|6|7|M|8|@]|9|@]|A|$]]|$1|I|3|-4|5|6|7|N|8|@]|9|@]|A|$]]]|J|$]]

I have use the command listed below in the recent past and it seems to work fine.

<pre><code>ActiveWorkbook.RefreshAll
</code></pre>

Hope that helps.

blocks|key|1763379|text|ActiveWorkbook.RefreshAll刷新所有内容，不仅包括数据透视表，还包括ODBC查询。我有几个引用数据连接的VBA查询，当该命令在没有VBA提供的详细信息的情况下运行数据连接时，使用此选项会崩溃|type|unstyled|depth|inlineStyleRanges|offset|length|style|CODE|entityRanges|data|1763380|如果您只想刷新透视图，我建议您使用此选项|1763381|Sub+RefreshPivotTables()+++++
++Dim+pivotTable+As+PivotTable+++++
++For+Each+pivotTable+In+ActiveSheet.PivotTables+++++++++
++++pivotTable.RefreshTable+++++
++Next+
End+Sub+|code-block|syntax|javascript|1763382|entityMap^0|0|P|0|0|0^^$0|@$1|2|3|4|5|6|7|O|8|@$9|P|A|Q|B|C]]|D|@]|E|$]]|$1|F|3|G|5|6|7|R|8|@]|D|@]|E|$]]|$1|H|3|I|5|J|7|S|8|@]|D|@]|E|$K|L]]|$1|M|3|-4|5|6|7|T|8|@]|D|@]|E|$]]]|N|$]]

<code>ActiveWorkbook.RefreshAll</code> refreshes everything, not only the pivot tables but also the ODBC queries. I have a couple of VBA queries that refer to Data connections and using this option crashes as the command runs the Data connections without the detail supplied from the VBA

I recommend the option if you only want the pivots refreshed

<pre><code>Sub RefreshPivotTables() 
 Dim pivotTable As PivotTable 
 For Each pivotTable In ActiveSheet.PivotTables 
 pivotTable.RefreshTable 
 Next 
End Sub 
</code></pre>

blocks|key|1763430|text|代码|type|unstyled|depth|inlineStyleRanges|entityRanges|data|1763431|Private+Sub+Worksheet_Activate()
++++Dim+PvtTbl+As+PivotTable
++++++++Cells.EntireColumn.AutoFit
++++++++For+Each+PvtTbl+In+Worksheets("Sales+Details").PivotTables
++++++++PvtTbl.RefreshTable
++++++++Next
End+Sub+|code-block|syntax|javascript|1763432|工作正常。|1763433|该代码用于激活工作表模块，因此当工作表被激活时，它会显示闪烁/毛刺。|1763434|entityMap^0|0|0|0|0^^$0|@$1|2|3|4|5|6|7|M|8|@]|9|@]|A|$]]|$1|B|3|C|5|D|7|N|8|@]|9|@]|A|$E|F]]|$1|G|3|H|5|6|7|O|8|@]|9|@]|A|$]]|$1|I|3|J|5|6|7|P|8|@]|9|@]|A|$]]|$1|K|3|-4|5|6|7|Q|8|@]|9|@]|A|$]]]|L|$]]

The code 

<pre><code>Private Sub Worksheet_Activate()
 Dim PvtTbl As PivotTable
 Cells.EntireColumn.AutoFit
 For Each PvtTbl In Worksheets("Sales Details").PivotTables
 PvtTbl.RefreshTable
 Next
End Sub 
</code></pre>

works fine.

The code is used in the activate sheet module, thus it displays a flicker/glitch when the sheet is activated.

I have a workbook with 20 different pivot tables. Is there any easy way to find all the pivot tables and refresh them in VBA?

Refreshing all the pivot tables in my excel workbook with a macro

我有一个工作簿，里面有20个不同的透视表。有没有什么简单的方法可以在VBA中找到并刷新所有的数据透视表？

问正在使用宏刷新excel工作簿中的所有透视表
EN

社区

活动

资源

关于

腾讯云开发者

热门产品

热门推荐

更多推荐

问正在使用宏刷新excel工作簿中的所有透视表EN

社区

活动

资源

关于

腾讯云开发者

热门产品

热门推荐

更多推荐

问正在使用宏刷新excel工作簿中的所有透视表
EN