通过VBA更新PivotTables的DataSource，不创建新的PivotCaches

Question

我有一个工作簿，其中有一个RawData表，还有8-9个具有从RawData读取表的透视表的其他表。RawData每天都在变化，也就是说，RawData表的表中可以有更多或更少的行数。

现在，我使用Excel的ChangeDataSource特性在每个工作表中手动更新每个透视表的DataSource属性。然而，这是痛苦的。在web上爬行时，我发现了一次更新所有透视表的vba代码。我没有粘贴整个代码，但它看起来像这样。

ActiveSheet.PivotTables("PivotTable1").ChangePivotCache ActiveWorkbook.PivotCaches.Create(SourceType:=xlDatabase, SourceData:=<RawData-AddressRange>)

但这段代码所做的是，它为每个数据透视表创建新的数据透视表缓存，并增加文件的大小。但是，我不希望文件大小或数据透视表缓存的数量增加，而只是更改现有数据透视表/数据透视表缓存的dataSource并刷新它们。

Answer 1

为所有PivotTable设置单个PivotCache

Sub UpdatePivots()
  Dim ws As Worksheet, pivot As PivotTable, cache As PivotCache

  ' create a new pivot cache '
  Set cache = ThisWorkbook.PivotCaches.Create( _
      XlPivotTableSourceType.xlDatabase, _
      ThisWorkbook.Sheets("Sheet1").UsedRange)

  ' set the pivot cache for each pivot table
  For Each ws In ThisWorkbook.Worksheets
    For Each pivot In ws.PivotTables
      If cache.Index Then
        pivot.CacheIndex = cache.Index
      Else
        pivot.ChangePivotCache cache
      End If
    Next
  Next

  ' refresh the cache
  cache.Refresh
End Sub

Answer 2

只需将RawData转换为Excel Table (Ctrl +T是键盘快捷键)，然后将PivotTables指向该表。(您必须再次使用“更改数据源”按钮，才能将其从使用硬编码引用(如$A$1:$Z$1000 )更改为表引用(如Table1) )。

从那时起，每当您将新数据放入RawData时，表将自动扩展(或收缩)以容纳它，并且每当您单击刷新时，PivotTables将自动引用这些表，而不是硬编码的范围。

另请注意，如果PivotTables都基于完全相同的数据源，则刷新其中一个会刷新所有数据源。

Answer 3

您应该考虑使用动态命名范围来定义RawData。将所有数据透视表的数据源设置为动态命名范围，然后您需要做的就是单击data>refresh all更新所有数据透视表

以下是有关动态命名范围http://www.excel-easy.com/examples/dynamic-named-range.html的教程