☰

使用 VBA 更改数据透视表的数据源

tag-icon • tag-icon microsoft-excel pivot-table
使用 VBA 更改数据透视表的数据源

我正在寻找一个代码，可以将数据透视表的数据源从一个工作表更改为另一个工作表。数据源工作表完全相同，只是数据不同。

我有一个从互联网上获取的代码，但是它不会更改我的活动工作表的数据源，而是会更改我所有工作表上的数据源。

遗憾的是，我不太了解 VBA 编码，不知道如何更改代码以仅影响我的活动工作表，我希望有人可以帮助我。

Option Explicit

Sub ChangeDataSourceForAllPivotTables()

    Dim wb                  As Workbook
    Dim ws                  As Worksheet
    Dim pt                  As PivotTable
    Dim rSourceData         As Range

    If ActiveWorkbook Is Nothing Then Exit Sub

    On Error GoTo ErrHandler

    Set wb = ActiveWorkbook

    Set rSourceData = wb.Worksheets("Sheet1").Range("A1").CurrentRegion 'change the name of the worksheet accordingly

    For Each ws In wb.Worksheets
        For Each pt In ws.PivotTables
            pt.ChangePivotCache wb.PivotCaches.Create(xlDatabase, rSourceData.Address(, , , True))
            pt.RefreshTable
        Next pt
    Next ws

ExitTheSub:
    Set wb = Nothing
    Set ws = Nothing
    Set pt = Nothing
    Set rSourceData = Nothing

    Exit Sub

ErrHandler:
    MsgBox "Error " & Err.Number & ":  " & Err.Description, vbCritical, "Error"
    Resume ExitTheSub

End Sub

提前感谢您的帮助！

答案1

此代码将帮助您更改特定工作表中所有数据透视表的源数据。

Sub ChangePivotSourceData()

Dim pt As PivotTable

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

End Sub

怎么运行的：

  • Copy&Paste此代码为Standard Module。
  • Sheet并且Source Data名称是可编辑的。
  • 在此代码中数据是命名范围，你需要先创建跑步 每次你想改变时都要用到这个代码数据透视表的源数据。

答案2

我想按目的地拆分文档（例如），并自动创建带有相关数据透视图的各种特定文件（针对每个目的地）。如何每次更新新数据透视图的数据源（西班牙、意大利、法国……）？因为它们保留了所有目的地（欧洲）的原始数据源。谢谢

Dim pt As PivotTable
    Dim MyData As Excel.ListObject
   
    Set MyData = Application.Range(Europe).ListObject
   
For Each pt In ActiveWorkbook.Worksheets("Chart - City").PivotTables
         pt.ChangePivotCache ActiveWorkbook.PivotCaches.Create _
            (SourceType:=xlDatabase, SourceData:="MyData")
Next pt

相关内容