マクロでピボットテーブルを行う方法

質問

マクロ初心者です。よろしくお願い致します。

マクロでピボットテーブルにて集計を行いたいと思っています。
コマンドボタン1発で以下の全ての動作を行いたいです。

■動作詳細■
[OK]コマンドボタンで指定したファイルを開く。
[OK]集計しやすいようにファイル内のデータを加工。
[NG]指定して開いたファイル上でピボットテーブル集計ができない。

■NG要因として■
指定して開いたファイルのSheet1上のデータをピボットテーブル新規作成でSheet2に作成するので、集計する元のデータの場所(Sheet1)をマクロ上に記載する必要があると思うのですが、うまくいきませんでした。
どなたかお分かりになる方がいらっしゃいましたらご助言いただければと思います。よろしくお願いします。

■作成マクロ■
ActiveWorkbook.PivotCaches.Add(SourceType:=xlDatabase, SourceData:= _
"test01!C1:C8").CreatePivotTable TableDestination:="", TableName:= _
"ピボットテーブル2", DefaultVersion:=xlPivotTableVersion10
ActiveSheet.PivotTableWizard TableDestination:=ActiveSheet.Cells(3, 1)
ActiveSheet.Cells(3, 1).Select
With ActiveSheet.PivotTables("ピボットテーブル2").PivotFields("コード")
.Orientation = xlRowField
.Position = 1
End With
With ActiveSheet.PivotTables("ピボットテーブル2").PivotFields("色")
.Orientation = xlColumnField
.Position = 1
End With
ActiveSheet.PivotTables("ピボットテーブル2").AddDataField ActiveSheet.PivotTables( _
"ピボットテーブル2").PivotFields("売上"), "データの個数 / 売上数量", xlCount

一度、作成したピボットテーブルのデータをマクロにコピーしてコマンドボタンにペーストしました。
それによって参照してあるセルの位置もズレてしまったのでしょうか？
どこを直せばよいのか分からなくなってしまいました。
よろしくお願いいたします。

回答No.2

OtenkiAme · ベストアンサー

こんにちは。

> 指定して開いたファイルのSheet1上のデータをピボットテーブル新規作成でSheet2に作成するので、

指定して開いた直後ならActiveworkbookでいいと思います。
Sheet1上のデータですから、"test01!C1:C8"では、シート名が違います。
また、C1:C8では、作成するフィールド名が存在しないのではないでしょうか。

SourceData:=の指定は、セル範囲を固定した記述にしないで、その都度データ範囲が取得できるように変更する必要があります。

新規作成でSheet2に作成するとありますから、Sheet2がない時は「シートを作成」、Sheet2があった場合は、「セル上のデータ（ピボットテーブル）を削除」してからピボットテーブルを作成する処理にしたら如何でしょうか。

ということで、次のように編集できます。

Dim PvtSht As Worksheet

With ActiveWorkbook

　　On Error Resume Next
　　Set PvtSht = .Worksheets("Sheet2")
　　On Error GoTo 0
　　If PvtSht Is Nothing Then
　　　　Set PvtSht = .Worksheets.Add
　　　　PvtSht.Name = "Sheet2"
　　Else
　　　　PvtSht.Cells.Clear
　　　　PvtSht.Cells.ColumnWidth = PvtSht.StandardWidth
　　End If

　　.PivotCaches.Add(SourceType:=xlDatabase, _
　　　　SourceData:=.Worksheets("Sheet1").Range("A1").CurrentRegion.Address(exTernal:=True)) _
　　　　.CreatePivotTable TableDestination:=PvtSht.Name & "!R3C1", TableName:="ピボットテーブル2", _
　　　　DefaultVersion:=xlPivotTableVersion10

End With

With PvtSht.PivotTables("ピボットテーブル2")
　　.PivotFields("コード").Orientation = xlRowField
　　.PivotFields("色").Orientation = xlColumnField
　　.AddDataField .PivotFields("売上"), "データの個数 / 売上数量", xlCount
　　Application.Goto Reference:=.TableRange1.Range("A1")
End With

回答No.1

cistronezk

＞ActiveWorkbook.PivotCaches.Add(SourceType:=xlDatabase, SourceData:= _
＞"test01!C1:C8")

「SourceData:=」の後の文字列で、集計もとのデータの範囲を指定します。
この場合は「"test01!C1:C8"」つまりシートtest01のC1:C8となっています。
もし、Sheet1のA1:C100がデータの範囲なら「"Sheet1!A1:C100"」に変更します。