Home » エクセルマクロ・Excel VBAの使い方 » ピボットテーブル » ピボットテーブルの情報一覧を作成するExcelマクロ

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

対象:Excel2007, Excel2010, Excel2013, Windows版Excel2016

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

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

[スポンサードリンク]

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

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

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


Sub ピボットテーブルの情報一覧を作成する()

 Dim sh As Worksheet
 Dim pvt As PivotTable
 Dim n As Long ' 一覧を書き出す行番号

 Worksheets.Add Before:=Sheets(1)

 Range("A1:E1").Value = _
  Split("シート名;ピボット名;セル範囲;データソース;キャッシュIndex", ";")

 n = 2
 For Each sh In Worksheets
  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
   n = n + 1
  Next pvt
 Next sh

 Range("A1:E1").EntireColumn.AutoFit

End Sub

サンプルマクロの解説

まず一覧を作成するワークシートをブックの先頭に挿入して、
 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)

[スポンサードリンク]

Home » エクセルマクロ・Excel VBAの使い方 » ピボットテーブル » ピボットテーブルの情報一覧を作成するExcelマクロ

TrackBack:0

TrackBack URL

Home » エクセルマクロ・Excel VBAの使い方 » ピボットテーブル » ピボットテーブルの情報一覧を作成するExcelマクロ

「ピボットテーブル」の記事一覧

検索


Copyright © インストラクターのネタ帳 All Rights Reserved.

.