マクロでピボットテーブルを作成するには？

質問

現在、仕事の関係でエクセルを多用しており、
仕事を楽にするためマクロを勉強しています。

インターネットを駆使して、下記のコードを作ってみたのですが、
【】の範囲で「参照が正しくありません」とエラーが出てしまいます。
どこがおかしいかご教授いただけますでしょうか？

自動記録でベースを作った後、範囲の指定を
VBA上で行ったつもりです。
データの集計は「A」シートのA～G列まで、行は不定なので、
「A」シートの一番下までのデータを集計するようにしたいです。
縦に「要素」、内容に「金額」を集計するよう、
ピボットを記録しています。

Sub ピボットを行う()
'
Range("A1").Select
Range(Selection, Selection.End(xlDown)).Select
Range(Selection, Selection.End(xlToRight)).Select

【 ActiveWorkbook.PivotCaches.Add(SourceType:=xlDatabase, SourceData:= _
"A!Selection.Address").CreatePivotTable TableDestination:="", TableName:= _
"ピボットテーブル1"】
ActiveSheet.PivotTableWizard TableDestination:=ActiveSheet.Cells(3, 1)
ActiveSheet.Cells(3, 1).Select
ActiveSheet.PivotTables("ピボットテーブル1").SmallGrid = False
ActiveSheet.PivotTables("ピボットテーブル1").AddFields RowFields:="要素"
With ActiveSheet.PivotTables("ピボットテーブル1").PivotFields("金額")
.Orientation = xlDataField
.Caption = "合計 : 金額"
.Function = xlSum
End With
End Sub

よろしくお願いいたします。

回答No.1

pauNed · ベストアンサー

こんにちは。
>(SourceType:=xlDatabase, SourceData:="A!Selection.Address")
ここを
(SourceType:=xlDatabase, SourceData:= Selection.Address(external:=True))
と変更すればできます。

ついでに、SelectやActivateは選択状況に依存し、不安定です。
ActiveWorkbookは仕方ないのかもしれませんが

Sub sample()
　　Dim r As Range

　　With ActiveWorkbook
　　　　With .Sheets("A")
　　　　　　Set r = .Range("G1", .Range("A1").End(xlDown))
　　　　End With
　　　　With .PivotCaches.Add(SourceType:=xlDatabase, SourceData:=r.Address(external:=True))
　　　　　　With .CreatePivotTable(TableDestination:="")
　　　　　　　　.AddFields RowFields:="要素"
　　　　　　　　With .PivotFields("金額")
　　　　　　　　　　.Orientation = xlDataField
　　　　　　　　　　.Caption = "合計 : 金額"
　　　　　　　　　　.Function = xlSum
　　　　　　　　End With
　　　　　　End With
　　　　End With
　　End With
　　Set r = Nothing
End Sub

などとしたほうが良いかも^ ^
QNo.2097393も参考にしてみてください。

お礼

ご回答、ありがとうございました。
教えて頂いたマクロで出来るようになりました。大変助かります。

後学のため、コメントを載せようと思っているのですが、
頂いたマクロにて不明点がいくつかあります。
(下記コメントの(1)～(4)です)
いつでも結構ですので、ご返答頂けないでしょうか？
(分かる範囲で自分で記載しています)

Sub sample()
Dim r As Range　'(1)この式は何のためですか??

With ActiveWorkbook　
With .Sheets("A")　'Aシートを選ぶ　
Set r = .Range("G1", .Range("A1").End(xlDown))　'G1からA1の最下層までを選択
End With
With .PivotCaches.Add(SourceType:=xlDatabase, SourceData:=r.Address(external:=True))　'(2)ピボットの範囲はドコに記入されているのですか??
With .CreatePivotTable(TableDestination:="")　
.AddFields RowFields:="要素"　'ピボットの横列に要素を選択　
With .PivotFields("金額")　　'ピボットの内容に金額を選択
.Orientation = xlDataField
.Caption = "合計 : 金額"　　'金額を合計にする
.Function = xlSum
End With　'(3)End Withが四つも並ぶのはなぜですか？
End With
End With
End With
Set r = Nothing　'(4)この式の意味が分かりません
End Sub　

仕事で忙しい中の独学だと限界があるようです。。。
初歩的な質問かも知れませんが、ご回答頂ければ幸いです。

回答No.3

pauNed

■(2)について

>(2)ピボットの範囲はドコに記入されているのですか??
ピボットテーブルを作成する先。という意味ですよね。
答えは...『省略しています』

.CreatePivotTable(TableDestination:="")
この TableDestination で指定するのですが、上記のように省略すると
ピボットウィザード3/3で[新規ワークシート]にピボットを作成するのと同じ事になります。
指定することも可能です。

Sub sample2()ではあえて、
ActiveWorkbook.Sheets.Add
とシートを事前に追加し、（追加したシートは必ずActiveSheetになるので）
TableDestination:=ActiveWorkbook.ActiveSheet.Range("E5")
と指定してみました。

また、お使いのバージョンはxl2000だと思いますが、
xl2002以降だと、ピボットの元データ範囲の指定は
SourceData:=r
や
SourceData:=Range("G1", .Range("A1").End(xlDown))
というように、セル範囲を表すObjectで指定できます。
xl2000だと
r.Address(external:=True)というセル範囲を表すアドレス『文字列』で指定しないといけません。

これに対して作成先は
TableDestination:=Range("E5") などとセル範囲での指定です。

■もう一点。
一般機能の[名前の定義]を使ってピボット元データの範囲を可変にする事もできます。
Sheets("A")で[Ctrl]+[F3]キーで[名前の定義]ウィンドウが開きます。
[名前]欄に　　　database　(例えば）
[参照範囲]欄に　=OFFSET($A$1,0,0,COUNTA($A:$A),7)
[OK]
として、手作業でピボットを作成します。
ピボットウィザードの元データの参照範囲に database
とすると、毎回ピボットを作成するのではなくて
一度作ったピボットの[データの更新]をするだけで良いです。

■蛇足として【VBAの理解を深めるには】
1)意味がわからない語句などは、その語にキャレット（マウスカーソル）をあてて[F1]キー押下で
　HELPトピックにアクセスできます。（できない語句もあります）
2)全体を通してコードの動きを確認したい時は、VBEウィンドウを半画面の状態で、
　コード内で[F8]キー押下で１ステップずつ、Excel画面の動きも見ながら実行すると効果的です。
3)その時、[ローカルウィンドウ]を表示させておくと、変数の内容も確認できます。

>仕事で忙しい中の独学だと限界があるようです。。。
私も同じような環境でした^ ^
>仕事を楽にするため
これが何よりも上達の原動力になると思います。
では。がんばってください^ ^

回答No.2

pauNed

こんにちは。

■(1)(4)について

Dim r As Range
　で、Object型(Range)の『変数』として r を準備しています。

Set r = .Range("G1", .Range("A1").End(xlDown))
　でその r にセル範囲を一旦、代入して、その後SourceData指定で使用します。
　通常 SetしたObject型の変数領域は、そのプロシージャが終了すると
　自動的にリセットされるとされていますが、

Set r = Nothing
　で、明示的にそのObjectを破棄して後始末をしています。

『変数』というのは、容れモノ、というか、別名、というか。そんなようなものです。
実行効率や、可読性、メンテナンス性を良くするためなどに使います。
私の拙い説明より、

"VBA" "変数について"

でgoogle検索してみてください。そこから
http://oshiete1.goo.ne.jp/kotaeru.php3?q=2148713
http://pc.nikkeibp.co.jp/article/NPC/20070208/261421/
などを参考にされると良いですよ。

■(3)について

実行効率や可読性を良くするという意味ではWithステートメントも同じです。
あるObjectに対して処理する時、何度も呼び出さず、With ステートメントで一度だけ指定して呼び出し、
効率を上げるためにまとめるわけです。
前述のコードを簡単に階層化させると

┌With ActiveWorkbook
├─この間 .の前は ActiveWorkbook.を指す
│┌With .PivotCaches
│├─この間 .の前は ActiveWorkbook.PivotCaches.を指す
││┌With .CreatePivotTable
││├─この間 .の前は ActiveWorkbook.PivotCaches.CreatePivotTable.を指す
│││┌With .PivotFields("金額")
│││├─この間 .の前は ActiveWorkbook.PivotCaches.CreatePivotTable.PivotFields("金額").を指す
│││└End With
││└End With
│└End With
└End With

...な感じです。
前述コードを、むりやりWith ステートメントを使わず書くなら
Sub sample2()
　　ActiveWorkbook.Sheets.Add
　　ActiveWorkbook.PivotCaches.Add( _
　　　　　　SourceType:=xlDatabase, _
　　　　　　SourceData:="A!" & ActiveWorkbook.Sheets("A").Range( _
　　　　　　"G1", ActiveWorkbook.Sheets("A").Range("A1").End(xlDown)).Address _
　　　　　　).CreatePivotTable TableDestination:=ActiveWorkbook.ActiveSheet.Range("E5")
　　ActiveWorkbook.ActiveSheet.PivotTables(1).AddFields RowFields:="要素"
　　ActiveWorkbook.ActiveSheet.PivotTables(1).PivotFields("金額").Orientation = xlDataField
　　ActiveWorkbook.ActiveSheet.PivotTables(1).DataFields(1).Caption = "金額計"
　　ActiveWorkbook.ActiveSheet.PivotTables(1).DataFields(1).Function = xlSum
End Sub
と書けます。

『変数』『Withステートメント』については
『Excel特有のオブジェクト、メソッド、プロパティの理解』
http://www.asahi-net.or.jp/~ef2o-inue/vba_k/sub04_060.html
この辺りが参考になるかと^ ^

お礼

ご返信ありがとうございました。
詳しいご説明で大変分かりやすかったです。
DimとかWithはいろんなところで使えそうですね。

まだまだ勉強中なので、これからココで質問することもあると思いますが、そのときはまたお付き合い頂ければ幸いです。

今回は本当に助かりました。
ありがとうございました。