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

eyecatch_pvtrefresh

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

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

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

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

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

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

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

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メソッドを使ってピボットテーブルを更新させています。

EnableEventsプロパティの設定を忘れずに

上のコードには、ApplicationオブジェクトのEnableEventsプロパティが入っていますね。

これは、エクセルが、VBAで実行するピボットテーブルの更新をシートの更新とみなしてしまうようで、Worksheet.Changeイベント内がRefresh処理のコードだけでは、こんなエラーになってしまうのです。

enableevents,エラー

その為、EnableEventsプロパティを使って一旦イベント実行をOFFにし、Worksheet.Changeイベントでやりたいことが終わったら再度イベントが実行されるようにONにしています。

EnableEventsプロパティについはこちらの記事で紹介していますので、併せてご覧ください。

【エクセルVBA】Changeイベントと組み合わせてもっと便利に!検索にヒットした値をドロップダウンリストに表示する方法
エクセルのChangeイベントと、Validationオブジェクトを用いたドロップダウンリストを作成する方法を組み合わせて、セルに入力した値をマスタシートから検索し、検索にヒットした値を入力したセルのドロップダウンリストに表示させる方法をご紹介しています。この方法を使えば、入力作業が楽になるかもしれません。

ピボットテーブルの自動更新

では、上のコードを保存して、ピボットテーブルを自動更新させてみましょう。

更新されたことがわかりやすいように、表の1行目にある2019年8月1日の「すいか」レコードの売上を100,000に変更してみると…

この通り、ピボットテーブルが自動的に更新されましたね。

ピボットテーブル,更新

関係ないセルの変更でもピボットテーブルが更新されてしまう

さて、ここまでピボットテーブルの自動更新についてご紹介してきましたが、ここでご紹介したコードだと、ピボットテーブルのデータソース範囲外のセルを更新しても、ピボットテーブルの更新が実行されてしまいます。

データソース範囲以外が変更されたことでピボットテーブルの更新が実行されても、ピボットテーブルの値自体には影響はありません。

しかし、実行する必要のない処理はできるだけ省きたいもの。

データソースが更新された時のみ、ピボットテーブルを更新させる方法は無いものか…

大丈夫、ちゃんと方法がありますよ。

その方法は、次回の記事でご紹介していきます。

最後に

今回は、PivotTableオブジェクトのRefreshTableメソッドと、PivotCacheオブジェクトのRefreshメソッドという2つのメソッドのご紹介と、Worksheet.Changeイベントと組み合わせて、ピボットテーブルを自動更新させる方法をご紹介しました。

次回の記事では、ピボットテーブルのデータソース範囲が変更された場合のみ、ピボットテーブルを更新する方法をご紹介していきます。

それでは、最後までお読みいただきありがとうございました!

タイトルとURLをコピーしました