VBAの質問です

質問

ピボットテーブルを作成するには、

ActiveWorkbook.PivotCaches.Add(SourceType:=xlDatabase, SourceData:= _
"copy!R5C1:R128C27").CreatePivotTable TableDestination:="", TableName:= _
"ピボットテーブル1"
ActiveSheet.PivotTableWizard TableDestination:=ActiveSheet.Cells(3, 1)
ActiveSheet.Cells(3, 1).Select
ActiveSheet.PivotTables("ピボットテーブル1").SmallGrid = False
Range("F13").Select

みたいにしますよね。（記録しただけです。）

それで、テーブルを作る際に参照するデータ（上の例ではSourceData:="copy!R5C1:R128C27"の部分にあたります）を、固定された範囲ではなくて、そのときどきで与えられたデータによってフレキシブルに変えてくれるようにしたいんです。

ピボットテーブルを作成するときには、データ範囲のどこかのセルを選択した状態で、「データ→ピボットテーブルの…」を選ぶだけでいいですよね。

そんな感じで、僕の場合は、R128の部分を、その時のデータによって変わってくれるようにしたいんです。currentregionとか使って行の数を数えて、変数を定義してそれに格納して…とかやってみたんですが、結局SourceData:="copy!R5C1:R128C27"のところをどう書き直せばよいのか分かりません。

アドバイスよろしくお願いします。説明が分かりづらくて申し訳ありません。

回答No.1

Wendy02 · ベストアンサー

こんな風になると思います。

AA列の下までで取るなら、
mySource = Range("A5", Range("AA65536").End(xlUp)).Address(, , xlR1C1)

A列の下までで取るなら、
mySource = Range("A5", Range("A65536").End(xlUp).Offset(, 26)).Address(, , xlR1C1)

ただ、このままのコードですと、シートをSelect してからでないと、正しく範囲が取れません。

そして、
SourceData:="copy!" & MySouce

として、Pivotに与えます。
なお、CurreentRegion ですと、データの基点から連続していないと範囲が抜けてしまいます。
Range("A5").CurrentRegion.Address(,,xlR1C1)
でも、セルの間が抜けていなければ使えます。

回答No.2

imogasi

マウスで範囲指定した範囲のセルを捉える方法があります。
Sub test02()
a = Application.InputBox("範囲", Type:=8)
For Each cl In a
MsgBox cl
Next
End Sub
これで範囲を聞いてきたとき、マウスでセル範囲を指定すると、その範囲内のセルの値を表示します。それで、範囲が捕らえられることがわかります。
これが応用できないかやってみてください。