如何使用VBA更新新的和复制的工作簿上的数据透视表数据源

问题所在

不久前，我尝试编写一个宏，该宏获取一个包含大量销售人员销售数据的主销售文件，并为每个销售人员创建主文件的单独副本，并修剪掉其他人的销售数据。

我已经让一切正常工作，除了数据透视表数据源不会更新。在从原始工作簿创建的任何副本上，透视数据源保持链接到原始主数据，而不是更新到新工作簿。这破坏了每个数据透视表和数据透视图，基本上使副本毫无价值。

在最终找到有效的解决方案之前，我尝试了几乎所有方法。

数据透视缓存与数据透视表

作为数据透视缓存和数据透视表之间区别的快速概述，数据透视缓存保存数据表的副本，当数据透视表连接到缓存时，对数据透视表所做的任何更改实际上都会对缓存进行，从而保留原始数据。

从透视到缓存的此链接是复制工作簿时中断的内容。数据透视表正在更新，但表连接到的缓存仍链接到原始工作簿。

这就是正在发生的事情;正如您在数据源下看到的，它连接到的表位于外部的“SalesWorkbook”工作簿中，而不是新工作簿中：

这是它应该的样子，使用其自己的工作簿中的 SalesDataTable，而不是主控形状的销售表：

修复

最终奏效的是重命名每个单独的数据透视缓存。此子循环遍历工作簿中的每个数据透视表，拉入完整的数据源名称（在本例中为“SalesWorkbook”！SalesDataTable），然后将位于“！”字符右侧的任何内容放入数组中，然后用于重命名所有数据透视缓存的源数据。

这基本上重命名了“销售工作簿”中的每个透视源！SalesDataTable ，成这个：SalesDataTable 。确保从活动工作簿中的数据正确更新缓存和表。

以下是完整的代码片段：

Sub updatePivotTables (wb As Workbook)

    Dim pt As PivotTable, ws As Worksheet, array
    For Each ws In wb.Sheets
        For Each pt In ws.PivotTables
            array = Split(pt.PivotCache.SourceData, "!")
            If UBound(array) = 1 Then
               pt.ChangePivotCache wb.PivotCaches.Create(SourceType:=xlDatabase, SourceData:=array(1))
            End If
         Next
     Next
     
End Sub

通过在生成个性化报表的循环中调用此 sub，数据透视表都链接到新工作簿中的正确表和缓存。

问题解决了！