在Excel中更新多个数据透视表上的数据源

问题描述

是否有一种简单的方法可以同时更新单个Excel工作表上的多个数据透视表的数据源?

Is there an easy way to update the data source for multiple pivot tables on a single Excel sheet at the same time?

所有数据透视表都引用相同的命名范围，但是我需要创建另一个具有相同数据透视表但访问不同命名范围的工作表.

All of the pivot tables reference the same named range, but I need to create a second worksheet that has the same pivot tables, but accessing a different named range.

理想情况下，我希望能够执行某种搜索和替换操作(就像您可以对公式执行操作一样)，而不是手动更新每个单独的数据透视表.

Ideally I would like to be able to do some kind of search and replace operation (like you can do on formulae), rather than updating each individual pivot table by hand.

有什么建议吗?

推荐答案

以下VBA代码将更改单个工作表上所有数据透视表的数据源.

The following VBA code will change the data source of all pivot tables on a single worksheet.

您将需要使用新的数据透视表将Sheet2参数更新为工作表名称，并将Data2参数更新为新的命名范围.

You will need to update the Sheet2 parameter to the name of the sheet with your new pivot tables and the Data2 parameter to your new named range.

Sub Change_Pivot_Source()

    Dim pt As PivotTable

    For Each pt In ActiveWorkbook.Worksheets("Sheet2").PivotTables
             pt.ChangePivotCache ActiveWorkbook.PivotCaches.Create _
                (SourceType:=xlDatabase, SourceData:="Data2")
    Next pt

End Sub

这篇关于在Excel中更新多个数据透视表上的数据源的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！