マクロでピボットテーブルを作成

質問

マクロでピボットテーブを作成したいのですが、範囲指定がうまくいきません。
Workbooks.Open Filename:="C:\出庫実績.csv"
Range(Selection, Selection.End(xlDown)).Select
Range(Selection, Selection.End(xlToRight)).Select
ActiveWorkbook.PivotCaches.Add(SourceType:=xlDatabase, SourceData:= _
"出庫実績!R1C1:R458C24").CreatePivotTable TableDestination:="", TableName:= _
"ピボットテーブル1", DefaultVersion:=xlPivotTableVersion10
ActiveSheet.PivotTableWizard TableDestination:=ActiveSheet.Cells(3, 1)
ActiveSheet.Cells(3, 1).Select
ActiveSheet.PivotTables("ピボットテーブル1").PivotFields("店舗名称").Subtotals = Array( _
False, False, False, False, False, False, False, False, False, False, False, False)
ActiveSheet.PivotTables("ピボットテーブル1").AddFields RowFields:="店舗名称"
ActiveSheet.PivotTables("ピボットテーブル1").PivotFields("出庫数ケース").Orientation = _
xlDataField
出庫実績csvのデータは日々変わる為、範囲の選択がうまくいかず、ピボットテーブルの合計数が合いません。よろしくお願いします。

回答No.3

imogasi

＞の式はどこに入れればいいでしょうか？
マクロの記録をとれば判る。
下記ActiveWorkbook.PivotCaches.Add以下はマクロの記録。
Sub Macro2()
Worksheets("Sheet1").Activate
d = Range("a65536").End(xlUp).Row
c = Range("iv1").End(xlToLeft).Column
s = Range(Cells(1, 1), Cells(d, c)).Address(ReferenceStyle:=xlR1C1)
MsgBox s

ActiveWorkbook.PivotCaches.Add(SourceType:=xlDatabase, SourceData:= _
"Sheet1!" & s).CreatePivotTable TableDestination:="", TableName:= _
"ピボットテーブル2", DefaultVersion:=xlPivotTableVersion10
ActiveSheet.PivotTableWizard TableDestination:=ActiveSheet.Cells(3, 1)
ActiveSheet.Cells(3, 1).Select
ActiveSheet.PivotTables("ピボットテーブル2").AddFields RowFields:="コード１", _
ColumnFields:="コード２"
ActiveSheet.PivotTables("ピボットテーブル2").PivotFields("コード１").Orientation = _
xlDataField
Application.CommandBars("PivotTable").Visible = False
ActiveWorkbook.ShowPivotTableFieldList = False
End Sub
ーー
のSourceData:= のところで、データの範囲番地を「R１C1形式」で指定するらしいから
d = Range("a65536").End(xlUp).Row
c = Range("iv1").End(xlToLeft).Column
s = Range(Cells(1, 1), Cells(d, c)).Address(ReferenceStyle:=xlR1C1)
でそれを取得し、その　s を
SourceData:= _
"Sheet1!" & s).
のように使えばよい。
ーー
上記のテストデータ例はA1:B8
コード１ コード２
a x
b y
c u
d v
a z
b z
d x
結果は
データの個数 / コード１ コード２
コード１ u v x y z 総計
a 1 1 2
b 1 1 2
c 1 1
d 1 1 2
総計 1 1 2 1 2 7
のようなものでテストした。

回答No.2

pauNed

こんにちは。
>出庫実績csvのデータは日々変わる為、範囲の選択がうまくいかず...
に対して、ちょっと変わったアプローチで一案。

Sub try()
　　Dim sh As Worksheet

　　With ActiveWorkbook
　　　　Set sh = .Sheets.Add
　　　　With sh.QueryTables.Add(Connection:="TEXT;C:\出庫実績.csv", _
　　　　　　　　　　　　　　　　Destination:=sh.Range("A1"))
　　　　　　.Name = "database"
　　　　　　.RefreshStyle = xlOverwriteCells
　　　　　　.AdjustColumnWidth = False
　　　　　　.TextFilePlatform = xlWindows
　　　　　　.TextFileTextQualifier = xlTextQualifierDoubleQuote
　　　　　　.TextFileCommaDelimiter = True
　　　　　　.Refresh BackgroundQuery:=False
　　　　End With
　　　　With .PivotCaches.Add(SourceType:=xlDatabase, _
　　　　　　　　　　　　　　　SourceData:=sh.Name & "!database") _
　　　　　　　　　　　　　　　.CreatePivotTable(TableDestination:="")
　　　　　　.AddFields RowFields:="店舗名称"
　　　　　　.PivotFields("出庫数ケース").Orientation = xlDataField
　　　　End With
　　End With

　　Set sh = Nothing
End Sub

上のコードは

1)ActiveWorkbookにシートを追加。
2)追加したシートに、メニュー[データ][外部データの取り込み][テキストファイルのインポート]。
3)「外部データベース範囲」に"database"という名前をつける。（任意で可）
4)"database"という名前をつけた「外部データベース範囲」を元に、ピボットテーブルを作成。

...という事をやっています。これは、実は手作業でもできます。
そしてこれを一度実行して、[外部データ]と[ピボットテーブル]をつくっておくと、
ツールバー[外部データ]の中の[すべて更新]ボタンだけで
csvからデータを取り込み、ピボットも同時に更新できます。
つまり、マクロ不要な案です。

また、ご質問のコードは CurrentRegionプロパティを使うと、以下のように書けます。
（データベース形式の表であれば、CurrentRegionプロパティが便利です）

Sub try2()
　　Dim r As Range

　　With Workbooks.Open(Filename:="C:\出庫実績.csv")
　　　　Set r = .ActiveSheet.Range("A1").CurrentRegion
　　　　With .PivotCaches.Add(SourceType:=xlDatabase, _
　　　　　　　　　　　　　　　SourceData:=r.Address(external:=True)) _
　　　　　　　　　　　　　　　.CreatePivotTable(TableDestination:="")
　　　　　　.AddFields RowFields:="店舗名称"
　　　　　　.PivotFields("出庫数ケース").Orientation = xlDataField
　　　　End With
　　End With

　　Set r = Nothing
End Sub

ピボットテーブルを扱うVBAコードについては、QNo.2926805も参考にされると良いかもしれません。

お礼

こんばんわ
お礼が遅くなりました。CurrentRegionプロパティを使って
みると、うまく出来ました。
本当に助かりました。ありがとうございます。

回答No.1

pbforce

別途、範囲を求めて、"出庫実績!R1C1:R～C～"を指定してあげればいいでしょう。

Range("A1").End(xlDown).Row
Range("A1").End(xlToRight).Column

で求められます。

補足

回答ありがとうございます。
Range("A1").End(xlDown).Row
Range("A1").End(xlToRight).Column
の式はどこに入れればいいでしょうか？