エクセル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セルから右、下方向に向かってピボットテーブルが作成されます。

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

TableNameプロパティでピボットテーブル名を指定する

作成するピボットテーブルの名前を指定するプロパティです。

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

例えばこんな風に。

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

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

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

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

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

VBAコードはこちら。

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

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

CreatePivotTable ピボットテーブル

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

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

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

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

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

CreatePivotTable ピボットテーブル完成

はい、完成!

まとめ

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

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

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

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

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

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

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

The following two tabs change content below.
ノグチ

ノグチ

元製造子会社SE、ERPパッケージシステムコンサルタント(販売管理、購買管理、生産管理、設備管理他)。 業務システム保守・導入両方やった経験をお伝えすることで、少しでもシステムや手作業に悩まされる人が減ればいいなぁ、楽にお仕事してもらえたらいいなぁと日々夢見ております。