ピボットテーブルの接続先データを一括変更する方法

はじめに

　仕事でピボットの接続先データを大量に変更する必要が生じました。一つ一つ接続先を変えていっても良いのですが面倒くさい。なので、シート内にあるピボットの接続先を全て変更するVBAを作成しました。

前提

• 一つのシートに大量のピボットテーブルがある
• 既に接続しているデータと新しく接続したいデータのカラム名は同じだが、中身が違う。

VBAのコード

今回対象とするエクセルはワークシート「ピボット」に変更したい多くのピボットテーブルが含まれており、変更後のデータの名前を「data2」とします。(Excelでデータに名前を付ける方法は補足で後述)このとき、接続先を一括変換するVBAのスクリプトは以下のようになります。

Sub 一括変換()
    sheet_name = "ピボット" 'ピボットテーブルが含まれるシートの名前を入れる
    change_data = "data1" 'データ名を入れる
    
    Dim ws As Worksheet
    Dim pivot As PivotTable
    
    Set ws = Worksheets(sheet_name)
    
    For Each pivot In ws.PivotTables
        pivot.ChangePivotCache ActiveWorkbook.PivotCaches.Create(xlDatabase, change_data)
    Next
End Sub

補足

データに名前をつける方法

1. 名前をつけたいデータが含まれているセルを全て選択する。
2. 「数式」タブの「定義された名前」グループ内の「名前の定義」のタブを押下する。
3. タブ内の「名前の定義」を押下。
4. 「新しい名前」ポップアップ内の「名前(N):」に好きな名前を入れる。(今回はdata2を入れた)
5. 「参照範囲(R):」が名前をつけたいデータを表しているか確認後、「OK」を押下で完了。

なおデータにつけた名前は、「数式」タブの「定義された名前」グループ内の「名前の管理」で確認することができます。