ピポットテーブルのマクロについて

　　　A列

１　　果物
２　　りんご
３　　みかん
４　　みかん
５　　りんご
６　　メロン
７　　メロン
８　　ぶどう
９　　りんご
１０　みかん
１１　りんご
　　　　｜
　　　　｜
以下名称が続きます

ExcelのSheet1にある上記のようなデータを、Sheet2のA1に対して下記のような形式で
出力するのをマクロ化したいと考えております。

果物　　　集計

ぶどう　　１
みかん　　３
メロン　　２
りんご　　４
総計　　１０

通常通りピポットテーブルを行うのをマクロで記録した場合に、
下記のようになりますが、上手く実行できません。

Sub Macro4()

Columns("A:A").Select
ActiveWorkbook.PivotCaches.Add(SourceType:=xlDatabase, SourceData:= _
"Sheet1!C1").CreatePivotTable TableDestination:="[Book1.xls]Sheet2!R1C1", _
TableName:="ピボットテーブル3", DefaultVersion:=xlPivotTableVersion10
With ActiveSheet.PivotTables("ピボットテーブル3").PivotFields("果物")
.Orientation = xlRowField
.Position = 1
End With
ActiveSheet.PivotTables("ピボットテーブル3").AddDataField ActiveSheet.PivotTables( _
"ピボットテーブル3").PivotFields("果物"), "データの個数 / 果物", xlCount
Range("D6").Select
End Sub

列の名称は“果物”以外でも実行でき、A列の全てのデータをピポットテーブルで
集計できるようにしたいのですが、初心者のためなかなか上手く実行できません。
どなたか教えていただけますと幸いです。

No.2 ベストアンサー 回答者： end-u 回答日時： 2011/01/29 00:19

さて、汎用性を持たせるためにここから修正していくわけですが、

それには「変数」というものについて理解しておいたほうが良いです。
変動する範囲に対して柔軟に対応する事ができ、
『列の名称は“果物”以外でも実行』できるようにしたりもできます。
またはWithステートメントについても理解しておいたほうが良いです。
http://www.asahi-net.or.jp/~ef2o-inue/vba_k/sub0 …

簡単な例。
Sub test1()
　　Dim rng As Range
　　
　　With ActiveWorkbook
　　　　With .Sheets("Sheet1")
　　　　　　Set rng = .Range("A1", .Cells(.Rows.Count, 1).End(xlUp))
　　　　End With
　　End With
　　Application.Goto rng
　　Set rng = Nothing
End Sub
アクティブブックのSheet1のA1からA列最終データまでを取得するサンプルです。



これを使ってピボット元データを、一旦、Range型変数に入れたほうが判り易いです。
ついでにピボットの作成先も前もって指定したほうが判り易いです。

Sub try()
　　Dim data As Range　　　'元データ範囲
　　Dim dest As Range　　　'Pivot作成先
　　Dim pvt　As PivotTable

　　With ActiveWorkbook
　　　　With .Sheets("Sheet1")
　　　　　　Set data = .Range("A1", .Cells(.Rows.Count, 1).End(xlUp))
　　　　End With
　　　　'作成先をSheet2に限定するなら『.Sheets.Add』を『.Sheets("Sheet2")』に。
　　　　'その場合はSheet2の既存データを事前にクリアしておいたほうが良いです。
　　　　Set dest = .Sheets.Add.Range("A1")
　　　　Set pvt = .PivotCaches.Add(SourceType:=xlDatabase, _
　　　　　　　　　　　　　　　　　 SourceData:=data.Address(external:=True) _
　　　　　　　　　　　　　　　　　 ).CreatePivotTable(TableDestination:=dest)
　　End With

　　'以降は作成後のPivotTableに対する処理

　　'列フィールド
　　pvt.AddFields RowFields:=data.Item(1).Value
　　'データフィールド
　　With pvt.PivotFields(data.Item(1).Value)
　　　　.Orientation = xlDataField
　　　　.Function = xlCount
　　End With

　　Set pvt = Nothing
　　Set dest = Nothing
　　Set data = Nothing
End Sub

VBEウィンドウからマクロを実行する時、
コード内で[F8]キーを押す事で１ステップずつ実行する事ができますから、
ワークシート上の動きも確認しながら理解に努めてください。

No.3 回答者： end-u 回答日時： 2011/01/29 00:21

失礼..

>　　'列フィールド
>　　pvt.AddFields RowFields:=data.Item(1).Value

行フィールド..の間違いでした..orz

No.1 回答者： end-u 回答日時： 2011/01/29 00:17

現在のコードを取り敢えず実行できるようにする最低限の修正にとどめるなら、

そのBookが[Book1.xls]だとして、
Sheet1のA列にデータがある場合、Sheet2を選択して


ActiveWorkbook.PivotCaches.Add(SourceType:=xlDatabase, SourceData:= _
"Sheet1!A:A").CreatePivotTable TableDestination:="[Book1.xls]Sheet2!R1C1", _
TableName:="ピボットテーブル3", DefaultVersion:=xlPivotTableVersion10
With ActiveSheet.PivotTables("ピボットテーブル3").PivotFields("果物")
.Orientation = xlRowField
.Position = 1
End With
With ActiveSheet.PivotTables("ピボットテーブル3").PivotFields("果物")
.Orientation = xlDataField
.Function = xlCount
End With


このように修正すれば動きます。


マクロ記録で
SourceData:= "Sheet1!C1"
と記録されたものはR1C1形式なのですが、
その記録されたマクロを実行する時にはA1形式だと判断されます。
つまりC1セル単独だと判断されるのでエラーになるわけです。