ピボットテーブルのソースデータの範囲を変数名で指定するには?

Question 13.3 Previous Next
MacのExcel98ですが、ピボットテーブルのマクロをキー記録で作成しましたら、下記のようになりました。

Sub 支出合計()
 ActiveSheet.PivotTableWizard SourceType:=xlDatabase,
SourceData:="Data!R2C1:R67C8",
  TableDestination:="", TableName:="ピボットテーブル1"
 ActiveSheet.PivotTables("ピボットテーブル1").AddFields RowFields:="費目", _
  ColumnFields:="月", PageFields:="年"
 With ActiveSheet.PivotTables("ピボットテーブル1").PivotFields("支出")
  .Orientation = xlDataField
  .Name = "合計 : 支出"
  .Function = xlSum
 End With
End Sub

SourceData:= "Data!R2C1:R67C8"の67行の67を、A列の最下端の位置を自動的に変数で処理したいのですが、教えて頂きたくお願いします。

(注)赤字の部分は、わかりやすく説明するために、色付けしました。
Answer   Copyright (C) 1998.11.22 永井善王
「A列の最下端の位置」を検出する方法は、 Macroの「範囲選択」のページに 「2) 範囲を検出して選択」 として掲載してあります。 罫線のない表と、罫線ある表の、どちらでも検出できます。まず、検出方法を理解してください。

次に変数の書き方ですが、MACROの「範囲選択」のページの
「4) 変数名で範囲指定して選択する」 に掲載してあるとおり、文字列と文字列を「&」でつないで作ります。

この質問の場合は、第1ステップで、「67行の67」の部分の行数を、例えば「下端」と名づけた変数に格納し、
第2ステップで、ヒボットテーブルの「SourceData」の書式に合わせた文字列を作ります。 つまり、67の部分を「下端」という変数に置きかえれば良いのですから、
ソースデータ範囲 = "Data!R2C1:R" & 下端 & "C8" となります。
そして、自動記録してできたマクロの一部(赤字の部分の =の右辺)を、この「ソースデータ範囲」という新たな変数に書き換えます。

整理すると、下記のようになります。
Sub 支出合計()
  Sheets("Data").Select
  下端 = Range(Cells(1, 1), Cells(1, 1)).End(xlDown).Row
  ソースデータ範囲 = "Data!R2C1:R" & 下端 & "C8"
  ActiveSheet.PivotTableWizard SourceType:=xlDatabase, SourceData:= _
    ソースデータ範囲, TableDestination:="", TableName:="ピボットテーブル1"
(以下同じ)
なお、どのシートの、どの位置にピボットテーブルを作るかなど、マクロの調整が必要であれば行ってください。

 

Excel VBA Macro