☰

使用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

相關內容