VBAでデータからピボットテーブルを自動作成

VBAでピボットテーブルを作成する

ピボットテーブルは挿入タブのピボットテーブルから手動で作成できますが、VBAを使って自動で作成することもできます。

例えば、データシートからピボットテーブルを作成するVBAコード例は下のような感じです。

Sub main()
    Dim endRow          As Long
    Dim endColumn       As Integer
    Dim stockSheet      As Worksheet
    Dim dataSheet       As Worksheet
    Dim startCell       As Range
    Dim dataStartCell   As Range
    Dim PCache          As PivotCache
    
    Set stockSheet = ThisWorkbook.Worksheets(1) 'ピボットテーブルを作りたいシート
    Set dataSheet = ThisWorkbook.Worksheets(2) '元データシート
    Set startCell = stockSheet.Range("startCell")
    Set dataStartCell = dataSheet.Range("dataStartCell")
    
    dataSheet.Activate
    
    ' データシートの最終行、最終列を取得
    endRow = dataSheet.Cells(Rows.Count, dataStartCell.Column).End(xlUp).Row
    endColumn = dataSheet.Cells(dataStartCell.Row, Columns.Count).End(xlToLeft).Column
    
    ' データシートからピボットキャッシュを作成
    Set PCache = ActiveWorkbook.PivotCaches.Create( _
        SourceType:=xlDatabase, _
        SourceData:=dataSheet.Range(Cells(dataStartCell.Row, dataStartCell.Column), Cells(endRow, endColumn)))
        
    ' ピボットテーブル作成
    PCache.CreatePivotTable _
        TableDestination:=stockSheet.Cells(startCell.Row + 1, startCell.Column), _
        TableName:="ピボットテーブル"

End Sub

シートとセルのオブジェクトを取得しておく

まず、シートのオブジェクトと開始位置のセルオブジェクトを取得しています。Worksheets()メソッドはシート名を渡すとそのシートを取得できます。

下のような感じで数値を渡すと、例えば1なら1番目のシートを取得できます。この場合だと2番目のシートに元データが置いてある状況です。

また、基準となるセルをRange()で取得しています。これは必須ではないですが、やっておくと楽です。例では名前を付けたセルを取得していますが、普通にRange(“A1”)とかでも大丈夫です。

Set stockSheet = ThisWorkbook.Worksheets(1) 'ピボットテーブルを作りたいシート
Set dataSheet = ThisWorkbook.Worksheets(2) '元データシート
Set startCell = stockSheet.Range("startCell")
Set dataStartCell = dataSheet.Range("dataStartCell")

データ範囲を指定してピボットキャッシュを定義

ピボットテーブルを作るにはピボットキャッシュというのを定義します。

下のようにSourceType, SourceDateを引数に渡します。SourceDataは元となるデータの範囲を指定します。

' データシートからピボットキャッシュを作成
Set PCache = ActiveWorkbook.PivotCaches.Create( _
    SourceType:=xlDatabase, _
    SourceData:=dataSheet.Range(Cells(dataStartCell.Row, dataStartCell.Column), Cells(endRow, endColumn)))

位置を指定してピボットテーブルを作成

ピボットキャッシュを定義したら、あとはピボットテーブルを表示する位置を指定してピボットテーブルを描画します。

' ピボットテーブル作成
PCache.CreatePivotTable _
    TableDestination:=stockSheet.Cells(startCell.Row + 1, startCell.Column), _
    TableName:="ピボットテーブル"

TableNameはピボットテーブルの名前です。ピボットテーブルの操作をする場合には使うことになるかと思います。

以上のコードでとりあえずピボットテーブルが作成されます。ただ、行や列に表示するデータを指定していないので空のピボットテーブルが表示されます。

行や列も自動でセットしたい場合は、次の記事を参照してください。

VBAを使ってピボットテーブルに行と列を設定する
VBAでピボットテーブルに行と列を設定 VBAで作成したピボットテーブルに行と列を設定する方法です。VBAでピボットテーブルを作成する方法は下記を参照してください。 ピボットテーブルに行と列を設定するコード例は下のような感じです。(...

コメント