【エクセルVBA】RefreshメソッドとRefreshTableメソッドでピボットテーブルを自動更新させてみよう

皆様こんにちは、ノグチです。

エクセルのピボットテーブル、便利ですよね。

ちょっとした集計表ならピボットテーブルで十分！というケースも多いかと思います。

でも、時々ピボットテーブルの更新をするのを忘れて、「あれ？数値がおかしい…」なんて思ってしまうこと、ありませんか？

あるいは、「シートの値を更新したら、自動的にピボットテーブルも更新してくれないかなあ」と思ったりしませんか？

そんな時は、VBAを使うと便利ですよ！

ということで今回は、VBAでピボットテーブルを自動更新させる方法をご紹介していきます。

VBAでピボットテーブルを更新する2通りの方法
PivotCacheオブジェクトのRefreshメソッド
1. Refreshメソッドの記述方法
2. Refreshメソッドの使用例
PivotTableオブジェクトのRefreshTableメソッド
1. RefreshTableメソッドの記述方法
2. RefreshTableメソッドの使用例
Worksheets.Changeイベントでピボットテーブルを自動更新させる
ピボットテーブルを自動更新させるコード
1. EnableEventsプロパティの設定を忘れずに
ピボットテーブルの自動更新
関係ないセルの変更でもピボットテーブルが更新されてしまう
最後に

VBAでピボットテーブルを更新する2通りの方法

まず、VBAでピボットテーブルを更新する方法は、以下の2つがあります。

PivotCacheオブジェクトのRefreshメソッドで更新する
PivotTableオブジェクトのRefreshTableメソッドで更新する

上記2つのメソッドとも、手動でピボットテーブルの「更新」をクリックした時と同様、ピボットテーブルを更新してくれるメソッドです。

では、2つの違いや記述方法を見ていきましょう。

PivotCacheオブジェクトのRefreshメソッド

Refreshメソッドの記述方法

まずはPivotCacheオブジェクトのRefreshメソッド。

このメソッドは、ピボットテーブルのデータソースをもう一度読み込んで、ピボットテーブルの値を更新してくれます。

記述方法はこちら。

PivotCacheオブジェクト.Refresh

Refreshメソッドの使用例

記述例はこんな感じです。

Sub Pivot_Refresh

With ActiveSheet
    .PivotTables("ピボットテーブル1").PivotCache.Refresh
End With

End Sub

シート上にある「ピボットテーブル1」というピボットテーブルを更新する、というコードです。

実に単純ですね。

PivotTableオブジェクトのRefreshTableメソッド

RefreshTableメソッドの記述方法

お次はPivotTableオブジェクトのRefreshTableメソッド。

こちらもピボットテーブルを更新してくれるメソッドです。

上でご紹介したRefreshメソッドとの大きな違いは、返り値があるということ。

ピボットテーブルの更新ができればTrue、そうでなければFalseの返り値をBoolean型で返してくれます。

記述方法はこちら。

PivotTableオブジェクト.RefreshTable

RefreshTableメソッドの使用例

このメソッドは返り値を返してくれるので、ピボットテーブルを更新できたことを前提とした処理をさせたい場合は、このメソッドを使って、「返り値がTrueだったら」という記述ができますね。

例えばこんな風に。

Sub Refresh_by_RefreshTable
Dim blnRefresh as Boolean

blnrefresh = .PivotTables("ピボットテーブル1").RefreshTable
  
If blnRefresh = True Then
    MsgBox "更新しました！"
End If
End Sub

Worksheets.Changeイベントでピボットテーブルを自動更新させる

ピボットテーブルをVBAで更新する方法をご紹介したところで、今回の主題である、ピボットテーブルを自動更新する方法を考えていきましょう。

ピボットテーブルを更新したいタイミングは、データソース範囲内の値が変更されたときですよね。

ということは、ワークシート上の値が変更されたということなので、Worksheets.Changeイベントを使えば、自動更新ができますね。

ということで、こちらのピボットテーブルを、Worksheets.Changeイベントと、PivotCacheオブジェクトのRefreshメソッドを組み合わせて、ピボットテーブルを自動更新させてみましょう。

A~C列のデータを、F~G列の「売上」というピボットテーブルにまとめています。

ピボットテーブルを自動更新させるコード

上記のワークシートが更新された時に、「売上」ピボットテーブルを自動更新させるコードがこちら。

Private Sub Worksheet_Change(ByVal Target As Range)

Application.EnableEvents = False

With ActiveSheet
    .PivotTables("売上").PivotCache.Refresh
End With

Application.EnableEvents = True
End Sub

Worksheet.Changeイベント内で、PivotCacheオブジェクトのRefreshメソッドを使ってピボットテーブルを更新させています。