エクセルVBAでピボットテーブルを作成! CreatePivotTableメソッド

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

前回、前々回でエクセルVBAでのピボットテーブル作成に向けて、あれこれをお伝えしてきました。

今回は、 前回の記事でご紹介したピボットキャッシュを使って、エクセルVBAでピボットテーブルを作成する方法をご紹介します。

エクセルVBAでピボットテーブルを作成する大まかな流れは、

1. ピボットテーブルのデータソースからピボットキャッシュを作成
2. 作成したピボットキャッシュから、ピボットテーブルを作成

の2ステップです。

ステップ1については、前回の記事で紹介していますので、こちらをご覧ください。

エクセルVBAでピボットテーブルを作成！PivotCacheにデータソース範囲をセットする

ピボットテーブルをVBAで作成する1ステップ目、ピボットテーブルのデータソース範囲を指定する方法をご紹介しています。またVBAでピボットテーブルを作成する前提知識として、ピボットキャッシュとは？やデータソースからピボットテーブルが作成される...

tonari-it.com

今回はステップ2の、ピボットキャッシュからピボットテーブルを作成する方法についてご説明していきます。

CreatePivotTableメソッドとは

前回の記事で作ったPivotCacheオブジェクトを使ってピボットテーブルを作成するには、PivotCacheオブジェクトのCreatePivotTableメソッドを使います。

CreatePivotTableメソッドは、PivotCacheからピボットテーブルを作成するためのメソッドです。

記述方法はこちら。

Destinationオプションでピボットテーブルの位置を指定する

ピボットテーブルを作成する場所を指定するオプションで、必ず指定します。

指定する場所は、作成するピボットテーブルの左上の端にあたるセルを指定します。

例えば、以下のコードの場合、アクティブになっているシートのA5セルから右、下方向に向かってピボットテーブルが作成されます。

記述方法は、こんな感じです。

PivotCache.CreatePivotTable　TableDestination:= Activesheets.Range("A5")

TableNameオプションでピボットテーブル名を指定する

作成するピボットテーブルの名前を指定するオプションです。

名前を指定する場合は、名前を””で囲って記述します。

例えばこんな風に。

PivotCache.CreatePivotTable TableDestination:= Activesheets.Range("A5"),  TableName:= "月別仕入表"

必須項目ではないので必ず指定する必要はなく、指定しない場合作成されたピボットテーブルは「ピボットテーブルxx」のような名前になります。

CreatePivotTableメソッドでピボットテーブル作成

ピボットテーブル作成のプログラム

前回の記事でご紹介したPivotChacheオブジェクトと、上でご説明したCreatePivotTableメソッドを使って、『データ』シートのデータから、『ピボットテーブル』シートに『月別仕入表』という名前のピボットテーブルを作成してみます。

データソースは、『データ』というシートのA1～E20のセル範囲にある仕入リストを使います。

VBAコードはこちら。

Sub Create_PivotTable()
Dim DataS As Worksheet 'データシート
Dim PivotS As Worksheet 'ピボットテーブルを作成するシート
Dim PCache As PivotCache 'ピボットキャッシュ用変数

Set DataS = ThisWorkbook.Worksheets("データ")

'『データ』シートからピボットキャッシュを作成
Set PCache = ActiveWorkbook.PivotCaches.Create( _
　　　　　　　SourceType:=xlDatabase, _
  　　　　　　SourceData:=DataS.Range("A1:E20"))

'『ピボットテーブル』シートを追加
Worksheets.Add
ActiveSheet.Name = "ピボットテーブル"
Set PivotS = ThisWorkbook.Worksheets("ピボットテーブル")

'『ピボットテーブル』シートにピボットテーブル作成
PCache.CreatePivotTable _
　　　　TableDestination:=PivotS.Range("A1"), _
 　　　 TableName:="月別仕入表"

End Sub

ピボットテーブル作成プログラム実行結果

上のVBAコードを実行してみると…

『ピボットテーブル』シートに、「月別仕入表」という名称でピボットテーブルが作成されていますね。

データソース範囲も確認してみましょう。

ピボットテーブルツールの「分析」タブから「データの変更」を選択して、作成したピボットテーブルのデータソース範囲を確認してみると・・・

VBAコードでピボットキャッシュの範囲に指定した通り、『データ』シートのA1～E20セルがデータソースになっています。

これで、エクセルVBAでピボットテーブルが作成できました！

あとは手動でピボットテーブルを作成したときと同じように行や列、値のフィールドを指定して、レイアウトを整えれば・・・

はい、完成！

まとめ

いかがでしたでしょうか。

今回は、データソースから作成したピボットキャッシュを使って、ピボットテーブルを作成する方法をご紹介しました。

今回ご紹介した方法では、VBA実行後にできたピボットテーブルは真っ白でしたが、もちろん行、列、値のフィールドをVBAで設定することもできますよ。

その方法はまだ別の機会にご紹介します。

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

連載目次：エクセルVBAでピボットテーブルを操作する第一歩

ピボットテーブルはとっても便利です。それを自動化できたらもっと便利に使えるのでは？ということで、VBAを使ってピボットテーブルを操作する方法についてお伝えしていきます。

1. エクセルVBAでピボットテーブルを操作するための第一歩～ピボットテーブルオブジェクト変数～
2. エクセルVBAでピボットテーブルを作成！PivotCacheにデータソース範囲をセットする
3. エクセルVBAでピボットテーブルを作成! CreatePivotTableメソッド
4. 【エクセルVBA】 ピボットテーブルに行、列、値フィールドを追加する