【VBA】ピボットテーブルの更新をVBAで自動化する方法（コピペOK）

この記事でわかること

• VBAでピボットテーブルを自動更新できる（PivotTable.RefreshTable）
• ブック内の全ピボットテーブルを一括で更新できる
• データソース範囲の自動拡張＋ブックを開いたときに自動更新する仕組みを作れる

対象: Excel 2016以降 / Microsoft 365、Windows 10/11

どんな場面で使う？

• 月次報告のピボットテーブルを毎回手動で更新するのが面倒なとき
• ブック内に複数あるピボットテーブルをまとめて一括更新したいとき
• データ行が増えたときにソース範囲の拡張も自動化したいとき
• ブックを開くだけでピボットが最新状態になる仕組みを作りたいとき

—

完成イメージ（Before / After）

Before（手作業でピボットテーブルを更新）:

After（VBAで自動更新）:

—

自分も以前、月次報告用のブックに5つほどピボットテーブルがあって、毎回手作業で1つずつ右クリック→「更新」を繰り返していた。正直めんどくさかったし、1つ更新し忘れたまま上司に提出して「このデータ古くない？」と指摘されたこともある。VBAで全ピボットを一括更新するようにしてからは、更新忘れがゼロになった。この記事で、同じ手間を抱えている人がサクッと自動化できるようになればうれしい。

ピボットテーブルの更新忘れは事故のもと。VBAで自動化すれば確実に最新データで報告できる。

なお、ピボットテーブルの作成自体をVBAで自動化したい場合は ピボットテーブルをVBAで自動生成する方法 を参照。

—

実行前の準備

バックアップを取る

ピボットテーブルの更新はデータの書き換えを伴う。 元データが壊れていると、ピボットの集計結果もおかしくなる。必ずファイルのコピーを別フォルダに保存してから実行する。

Excelをマクロ有効ブック（.xlsm）で保存する

拡張子が .xlsx のままだとマクロが保存できない。

1. 「ファイル」→「名前を付けて保存」
2. ファイルの種類を「Excelマクロ有効ブック (*.xlsm)」に変更
3. 保存

VBE（コードを書く画面）を開く

1. Alt + F11 でVBEを開く
2. 「挿入」→「標準モジュール」をクリック
3. 表示された白い画面にコードを貼り付ける

—

手順

ステップ1：コードを貼り付ける

下のコード（最小版）をVBEの標準モジュールに貼り付ける。

ステップ2：シート名・ピボットテーブル名を確認する

ピボットテーブルの名前は、ピボットテーブルをクリックしたときにリボンの「ピボットテーブル分析」タブの左上に表示される。コード内の "ピボットテーブル1" を自分のピボットテーブル名に書き換える。

ステップ3：マクロを実行する

Alt + F8 →「RefreshPivot」を選択 →「実行」。ピボットテーブルが最新データに更新される。

—

コード（最小版）：特定のピボットテーブルを更新する

Sub RefreshPivot()
    '--- 特定のピボットテーブルを更新する ---
    '貼り付け場所：標準モジュール
    '実行方法：Alt + F8 → RefreshPivot → 実行

    Dim ws As Worksheet
    Set ws = ThisWorkbook.Worksheets("Sheet1") '★シート名を変更

    ws.PivotTables("ピボットテーブル1").RefreshTable '★ピボットテーブル名を変更

    MsgBox "ピボットテーブルを更新しました。", vbInformation
End Sub

ポイント:

• RefreshTable メソッドが更新の本体。1行で完了する
• シート名とピボットテーブル名は自分の環境に合わせて書き換える

—

コード（実務版）：全ピボット一括更新＋データソース自動拡張＋ブック起動時に自動更新

自分はこの実務版を使うようになってから、月次報告の準備が劇的に楽になった。データを貼り付けてブックを開き直すだけで、全ピボットが最新状態になっている。同僚にも共有したら「これブック開くだけで更新されるの？」と驚かれた。

実務版コード1：全ピボット一括更新＋データソース自動拡張（標準モジュール）

Sub RefreshAllPivots()
    '--- ブック内の全ピボットテーブルを一括更新する ---
    '貼り付け場所：標準モジュール
    '実行方法：Alt + F8 → RefreshAllPivots → 実行

    Dim ws As Worksheet
    Dim pt As PivotTable
    Dim pc As PivotCache
    Dim srcWs As Worksheet
    Dim lastRow As Long
    Dim lastCol As Long
    Dim srcRange As Range
    Dim updatedCount As Long

    Application.ScreenUpdating = False '画面更新を停止して高速化

    '--- ここが追加：データソース範囲の自動拡張 ---
    'ピボットの元データがあるシートの範囲を自動検出して更新する
    For Each pc In ThisWorkbook.PivotCaches
        On Error Resume Next
        Set srcWs = Nothing
        '元データのシートを取得（エラーが出る場合はスキップ）
        Dim srcAddr As String
        srcAddr = pc.SourceData

        'SourceDataがシート参照の場合のみ処理
        If InStr(srcAddr, "!") > 0 Then
            Dim sheetName As String
            sheetName = Left(srcAddr, InStr(srcAddr, "!") - 1)
            sheetName = Replace(sheetName, "'", "") '囲みの'を除去
            Set srcWs = Nothing
            Set srcWs = ThisWorkbook.Worksheets(sheetName)

            If Not srcWs Is Nothing Then
                '最終行・最終列を取得してソース範囲を自動拡張
                lastRow = srcWs.Cells(srcWs.Rows.Count, 1).End(xlUp).Row
                lastCol = srcWs.Cells(1, srcWs.Columns.Count).End(xlToLeft).Column

                If lastRow >= 2 And lastCol >= 1 Then
                    Set srcRange = srcWs.Range(srcWs.Cells(1, 1), srcWs.Cells(lastRow, lastCol))
                    pc.SourceData = "'" & srcWs.Name & "'!" & srcRange.Address(ReferenceStyle:=xlR1C1)
                End If
            End If
        End If
        On Error GoTo 0
    Next pc
    '--- ここまで追加 ---

    '全シートの全ピボットテーブルを更新
    updatedCount = 0
    For Each ws In ThisWorkbook.Worksheets
        For Each pt In ws.PivotTables
            On Error Resume Next
            pt.RefreshTable
            If Err.Number = 0 Then
                updatedCount = updatedCount + 1
            Else
                Debug.Print "更新失敗: " & ws.Name & " - " & pt.Name & " (Error: " & Err.Description & ")"
                Err.Clear
            End If
            On Error GoTo 0
        Next pt
    Next ws

    Application.ScreenUpdating = True '画面更新を再開

    MsgBox updatedCount & " 個のピボットテーブルを更新しました。", vbInformation
End Sub

実務版コード2：ブックを開いたときに自動更新（ThisWorkbook モジュール）

'--- ブックを開いたときにピボットテーブルを自動更新する ---
'貼り付け場所：ThisWorkbook モジュール（標準モジュールではない！）
'VBEの左側ツリーで「ThisWorkbook」をダブルクリックして開く

Private Sub Workbook_Open()
    Dim ws As Worksheet
    Dim pt As PivotTable

    Application.ScreenUpdating = False

    For Each ws In ThisWorkbook.Worksheets
        For Each pt In ws.PivotTables
            On Error Resume Next
            pt.RefreshTable
            On Error GoTo 0
        Next pt
    Next ws

    Application.ScreenUpdating = True
End Sub

実務版のポイント:

• RefreshAllPivots はデータソース範囲を自動拡張してから更新するので、行が増えても安心
• Workbook_Open イベントでブックを開くだけで自動更新される
• エラー処理付きなので、1つのピボットが壊れていても他のピボットは更新される
• 画面更新・再計算を止めてマクロを高速化する方法 の ScreenUpdating を組み合わせて高速化している

ThisWorkbook モジュールへの貼り付け方:

VBEの左側「プロジェクトエクスプローラー」で ThisWorkbook をダブルクリック → 表示されたコード画面に貼り付ける。標準モジュールに貼り付けても動かないので注意。イベント処理の詳細は ブックを開いたとき・保存時に自動実行するイベント処理の方法 を参照。

—

落とし穴

自分も最初、ピボットテーブルの更新マクロを書いたとき「これだけ？簡単じゃん」と思っていたのに、実務で使い始めたら予想外のトラブルに何度もハマった。特に3番目のソース範囲ずれは、上司に報告した集計表の合計値がおかしくて冷や汗をかいた。

—

VBAでピボットテーブルの更新がエラーになるときの対処法

「RefreshTableを実行するとエラーが出る」という場合、原因はデータソースの範囲が無効になっていることが多い。データの行数が変わった場合、ソース範囲が古いまま。PivotCacheのSourceDataを最新の最終行に合わせて更新してからRefreshする。

VBAでピボットテーブルのソース範囲が自動拡張されないときの対処法

「データを追加したのにピボットに反映されない」という場合、原因はソース範囲が固定されていること。ChangePivotCacheでSourceDataを動的に設定するか、テーブル形式（ListObject）をソースにすれば自動でデータ範囲が拡張される。

FAQ

Q1. RefreshTableとRefreshAllの違いは？

PivotTable.RefreshTable は個別のピボットテーブルを更新する。ThisWorkbook.RefreshAll はピボットテーブルだけでなく、クエリテーブルや外部データ接続もすべて更新する。ピボットだけ更新したい場合は RefreshTable を使う。外部接続も含めて全部更新したい場合は RefreshAll が手軽。

Q2. ピボットテーブルの名前がわからない場合はどうする？

ピボットテーブルのセルをクリックすると、リボンの「ピボットテーブル分析」タブの左上に名前が表示される。VBAで一覧を取得したい場合は、イミディエイトウィンドウで For Each pt In ActiveSheet.PivotTables: Debug.Print pt.Name: Next を実行すると全ピボット名が出力される。

Q3. 元データをテーブル形式にしておけばソース範囲の自動拡張は不要？

その通り。元データを Ctrl + T でテーブル化しておけば、行を追加してもピボットのソース範囲が自動で追従する。テーブル操作の詳細は テーブル形式のデータをVBAで操作する方法 を参照。自分も最初はRange指定で苦労していたが、テーブル形式に変えてからは範囲ずれの問題がなくなった。

Q4. 特定のシートのピボットだけ更新したい場合は？

最小版コードのように、シート名を指定して For Each pt In ws.PivotTables でループすればよい。実務版コードの「全シートループ」部分を特定シートに限定するだけで対応できる。

Q5. セルの値が変わったときに自動でピボットを更新できる？

できる。Worksheet_Change イベント内で RefreshTable を呼び出せばよい。ただし、ピボットの元データがあるシートで Worksheet_Change を使うと、更新のたびにイベントが発火してループする可能性がある。Application.EnableEvents = False で制御が必要。詳細は セルの値が変わったら自動実行する方法 を参照。

—

—

まとめ

この記事では、VBAでピボットテーブルを自動更新する方法を解説した。

• 基本: PivotTable.RefreshTable で特定のピボットを更新
• 実務版: 全ブック内の全ピボットを一括更新＋データソース範囲の自動拡張
• 自動化: Workbook_Open イベントでブックを開くだけで最新状態にする

ピボットテーブルの更新忘れは報告ミスに直結する。VBAで自動化しておけば、常に最新データで集計できる。

元データのソース範囲ずれが心配な場合は、データを テーブル形式 にしておくのが最も確実な方法。

—

次にやりたくなること

• ピボットテーブルをVBAで自動生成する方法: ピボットテーブルの作成自体もVBAで自動化したい場合
• ブックを開いたとき・保存時に自動実行するイベント処理の方法: Workbook_Openで開くたびに自動更新する仕組みを作りたい場合
• 画面更新・再計算を止めてマクロを高速化する方法: 大量のピボット更新を高速化したい場合
• ピボットテーブル自体の作成も自動化したい → ピボットテーブルをVBAで自動生成する方法 で、フィールド配置からグラフ連動まで一括で作れる
• ブックを開いたときに他の処理も自動実行したい → ブックを開いたとき・保存時に自動実行するイベント処理の方法 で、Workbook_Openの活用パターンが分かる
• ピボットの元データが変わったらリアルタイムに反応させたい → セルの値が変わったら自動実行する方法 で、Worksheet_Changeイベントの使い方が分かる