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


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

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

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

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

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

の2ステップです。

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

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

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

スポンサーリンク

CreatePivotTableメソッドとは

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

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

記述方法はこちら。

PivotCacheオブジェクト.CreatePivotTable TableDestination:=位置指定, TableName:=ピボットテーブル名

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

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

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

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

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

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

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

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

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

例えばこんな風に。

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

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

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

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

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

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

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コードを実行してみると…

CreatePivotTable ピボットテーブル

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

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

ピボットテーブルツールの「分析」タブから「データの変更」を選択して、作成したピボットテーブルのデータソース範囲を確認してみると・・・
CreatePivotTable データソース
VBAコードでピボットキャッシュの範囲に指定した通り、『データ』シートのA1~E20セルがデータソースになっています。

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

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

CreatePivotTable ピボットテーブル完成

はい、完成!

まとめ

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

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

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

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

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

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

ピボットテーブルはとっても便利です。それを自動化できたらもっと便利に使えるのでは?ということで、VBAを使ってピボットテーブルを操作する方法についてお伝えしていきます。
  1. エクセルVBAでピボットテーブルを操作するための第一歩~ピボットテーブルオブジェクト変数~
  2. エクセルVBAでピボットテーブルを作成!PivotCacheにデータソース範囲をセットする
  3. エクセルVBAでピボットテーブルを作成! CreatePivotTableメソッド
  4. 【エクセルVBA】 ピボットテーブルに行、列、値フィールドを追加する

コメント

  1. ケンタロウ より:

    色んな、サイトで調べましたが、このサイトが一番詳しく、ためになるコンテンツでした。お陰で、問題が解決しました。ありがとうございました。

    • ノグチ より:

      ケンタロウさん

      嬉しいコメントありがとうございます!
      記事がお役に立ててたようで、こちらも嬉しい限りです!

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