VBA – ピボットテーブルを更新する

このチュートリアルでは、VBAを使用してピボットテーブルまたはすべてのピボットテーブルをリフレッシュする方法を説明します。

ピボットテーブルは、Excelの非常に強力なデータツールです。ピボットテーブルは、フィールドや行をグループ化したり要約したりすることで、大量のデータを分析することを可能にします。

ピボットテーブルを作成すると、データはピボットテーブルキャッシュと呼ばれるものに保存されます。

ピボットテーブルのデータソースが更新されても、ピボットテーブルそのものは更新されません。Excelのユーザーは、リボンの［データ］タブにある［すべて更新］ボタンをクリックして、ピボットテーブルのソースデータを更新する必要があります。

あるいは、VBAでマクロを書き、データを更新してもらうことも可能です

すべてのデータ接続を更新する

データを更新する最も簡単な方法は、VBAを使用して、リボンの「すべてを更新」ボタンが行うことを模倣することです。

Sub RefreshConnections()
    ActiveWorkbook.RefreshAll
End Sub

このメソッドは、アクティブにしているワークブック内のすべてのデータへの接続をリフレッシュします。 また、1つのピボットテーブルだけでなく、異なるデータセットに接続された複数のピボットテーブルがある場合は、複数のピボットテーブルを更新します。

すべてのピボットテーブルを更新する

ワークブック内のピボットテーブルのみを更新し、他のデータ接続を除外するには、RefreshTableというメソッドを使用します。

ワークブックに複数のピボットテーブルがある場合、すべてのピボットテーブルを更新するためには、すべてのピボットテーブルをループする必要があります。 そのために、まずPivot Table変数を宣言し、アクティブなワークブック内のすべてのピボットテーブルをループするFor Eachループを作成します。

Sub RefreshPivotsOnly()
  Dim tblPivot As PivotTable
  For Each tblPivot In ActiveWorkbook.PivotTables
     tblPivot.RefreshTable
  Next tblPivot
End Sub

同じようなマクロを使って、ワークブック全体ではなく、アクティブシートのピボットテーブルだけを更新することができます。ActiveWorkbookではなく、ActiveSheetのピボットテーブルをループさせるのです。

Sub RefreshActiveSheetPivotsOnly()
   Dim tblPivot As PivotTable
   For Each tblPivot In ActiveSheet.PivotTables
      tblPivot.RefreshTable
   次のtblPivot
End Sub

このマクロは、ActiveSheet上で簡単にアクセスできれば、最も便利なマクロです。そのためには、シート上にマクロを実行するためのボタンを作ればいいのです。

一つのピボットテーブルのみを更新する

ワークブック内の他のピボットテーブルを更新せず、作業中のピボットテーブルだけを更新したい場合、特定のピボットテーブルを特定する必要があります。もちろん、ピボットテーブルの名前（この場合はPivotTable1）がわかっていればOKです。

Sub RefreshOneTable
   ActiveSheet.PivotTables("PivotTable1").RefreshTable
End Sub

ピボットテーブルキャッシュの更新

ワークブックに複数のピボットテーブルがあり、それらがすべて同じデータを使用している場合、実際のピボットテーブルを更新するのではなく、ピボットテーブルのキャッシュを更新することができます。キャッシュを更新すると、キャッシュに含まれるデータを使用しているすべてのピボットテーブルが自動的に更新されます。

Sub RefreshCache()
   Dim chPivot As PivotCache
   For Each chPivot In ActiveWorkbook.PivotCaches
       chPivot.Refresh
   Next chPivot
End Sub