ピボットテーブルの情報一覧を作成するExcelマクロ

複数のピボットテーブルが存在しているブックを操作しているときに、どのワークシートにどんなピボットテーブルが存在しているのか、簡単に確認したいというシーンに遭遇しました。

そんなときに使えるExcelマクロを、VBA（Visual Basic for Applications）で作ったのでご紹介しておきます。

ピボットテーブルの情報一覧を作成するサンプルマクロ

以下のマクロを実行すると、アクティブなブックの先頭にワークシートが挿入され、そのブックに含まれるピボットテーブルの情報一覧が作成されます。

作成される項目は、
　ワークシート名
　ピボットテーブル名
　ピボットテーブルの存在するセル範囲
　データソース
　ピボットキャッシュのインデックス番号
です。

サンプルマクロの解説

まず一覧を作成するワークシートをブックの先頭に挿入して、
　Worksheets.Add Before:=Sheets(1)

一覧表の見出しを作成しています。
　Range("A1:E1").Value = _
　　Split("シート名;ピボット名;セル範囲;データソース;キャッシュIndex", ";")

PivotTableオブジェクトは、Worksheetオブジェクトの下位の階層に該当するオブジェクトですので、Worksheetsコレクションオブジェクトに対するFor Each～Nextループの中で、
　Dim sh As Worksheet
　For Each sh In Worksheets

各Worksheetに含まれる、PivotTablesコレクションオブジェクトに対してFor Each～Nextループを回して、
　Dim pvt As PivotTable
　　For Each pvt In sh.PivotTables

ワークシート名、
　　　Cells(n, "A").Value = sh.Name

ピボットテーブル名、
　　　Cells(n, "B").Value = pvt.Name

ピボットテーブルの存在するセル範囲、
　　　Cells(n, "C").Value = pvt.TableRange2.Address(False, False)

データソース（元データのセル範囲）、
　　　Cells(n, "D").Value = Application.ConvertFormula( _
　　　　　　　　　　　　　pvt.SourceData, xlR1C1, xlA1, xlRelative)

ピボットキャッシュのインデックス番号を、
　　　Cells(n, "E").Value = pvt.PivotCache.Index

書き出しています。

PivotTableオブジェクトのSourceDataプロパティは、R1C1形式でセル範囲を返してくるように作られていますので、Application.ConvertFormulaメソッドを使ってA1形式に変換してから書き出しています。
　　　Cells(n, "D").Value = Application.ConvertFormula( _
　　　　　　　　　　　　　pvt.SourceData, xlR1C1, xlA1, xlRelative)