ピボットテーブル完成後の最終行の取得をVBAで

質問

エクセル2003です。
あるデータからピボットテーブルを
作成し完成後、それの一部分を別シートに転記し
その後ピボットテーブルを削除する構文を作成しました。

●最終行 = .Cells(.Rows.Count, 1).End(xlUp).Row
の部分でエラーです。
・実行時エラー438
・オブジェクトは、このプロパディまたはメソッドをサポートしていません。

この実行文のオブジェクトは
ActiveSheet.PivotTables("ピボットテーブル1")
でこれがサポートをしていないという事でしょうか？
範囲が変動するデータを基にピボットテーブル作成時は
マクロの記録で作成した構文では対応できないので
書換えて対応出来たのですが
ピボットテーブルが完成すると基のデータと
最終行、列がかわります。
そこにも同じ仕組みを入れたつもりですが駄目でした。
ピボットテーブル作成後の最終行の行番号の取得方法を教えて下さい。
変数　最終行に代入したいです。お願いします。

Sub 編集転記削除()

Dim 範囲行
Dim 範囲列
Dim 範囲
Dim シート名
Dim 最終行
Dim 着手日

With Sheets("基データ")'基データのデータ範囲を取得
範囲行 = .Cells(.Rows.Count, 1).End(xlUp).Row
範囲列 = .Cells(1, Columns.Count).End(xlToLeft).Column
End With
範囲 = "基データ!R1C1:R" & 範囲行 & "C" & 範囲列

ActiveWorkbook.PivotCaches.Add(SourceType:=xlDatabase, SourceData:=範囲) _
.CreatePivotTable TableDestination:="", TableName:="ピボットテーブル1", _
DefaultVersion:=xlPivotTableVersion10
ActiveSheet.PivotTableWizard TableDestination:=ActiveSheet.Cells(3, 1)
ActiveSheet.Cells(3, 1).Select

'新規挿入されたシート名が変動するので変数に代入
シート名 = ActiveSheet.Name

With ActiveSheet.PivotTables("ピボットテーブル1")
.PivotFields("順番").Orientation = xlRowField
.PivotFields("製造番号").Orientation = xlRowField
.PivotFields("品名").Orientation = xlRowField
.PivotFields("納期").Orientation = xlRowField
.PivotFields("数量").Orientation = xlRowField
.PivotFields("品番").Orientation = xlRowField

.PivotFields("順番").Subtotals(1) = False
.PivotFields("製造番号").Subtotals(1) = False
.PivotFields("品名").Subtotals(1) = False
.PivotFields("納期").Subtotals(1) = False
.PivotFields("数量").Subtotals(1) = False
.PivotFields("品番").Subtotals(1) = False
.ColumnGrand = False
.RowGrand = True
'●ここでエラーになる↓
最終行 = .Cells(.Rows.Count, 1).End(xlUp).Row
Range(Range("B5"), Cells(最終行, 5)).Copy
Sheets("リスト").Range("B4").PasteSpecial Paste:=xlPasteValues
Range(Range("F5"), Cells(最終行, 6)).Copy
Sheets("リスト").Range("A4").PasteSpecial Paste:=xlPasteValues
End With
’ピボットテーブルを削除
Application.DisplayAlerts = False
Sheets(シート名).Delete
Application.DisplayAlerts = True
End Sub

回答No.1

keithin · ベストアンサー

間違ったWithの下にぶら下がってしまっているので，次のように修正します。

最終行 = activesheet.Cells(activesheet.Rows.Count, 1).End(xlUp).Row

＃いずれの修正箇所も，必要なのはピボットテーブルを置いた現在のシート全体から見たセルの情報であって，今のマクロでWithしているピボットテーブルのものではありません。

また，全体に「ActiveSheet」を使わずにピボットテーブルレポートを作成したシートをきちんと取得して操作するマクロにステップアップするよう，勉強してみて下さい。

お礼

教えていただいた
>最終行 = activesheet.Cells(activesheet.Rows.Count, 1).End(xlUp).Row

で最終行の取得が出来ました。

>全体に「ActiveSheet」を使わずにピボットテーブルレポートを
>作成したシートをきちんと取得して操作するマクロにステップアップ

With ActiveSheet.PivotTables("ピボットテーブル1")
以降ですが
マクロの記録では
Ｗith　～　End　With
が何行も出来たので1つに纏めたのですが。

ピボットテーブルは作成すると
その時の状況によって
Sheet1になったりSheet2になったり
Sheet9などの場合も有ります。
シート名をどうやって取得していいのか
よくわかりません。

どうもありがとうございました。

補足

以下の●の部分を変更したら
元の構文
最終行 = .Cells(.Rows.Count, 1).End(xlUp).Row
で動きました。

（先頭からここまで省略）

With ActiveSheet.PivotTables("ピボットテーブル1")
.PivotFields("順番").Orientation = xlRowField
.PivotFields("オーダーNo").Orientation = xlRowField
.PivotFields("親品名").Orientation = xlRowField
.PivotFields("開始日").Orientation = xlRowField
.PivotFields("オーダー数").Orientation = xlRowField
.PivotFields("親品番").Orientation = xlRowField
.PivotFields("順番").Subtotals(1) = False
.PivotFields("オーダーNo").Subtotals(1) = False
.PivotFields("親品名").Subtotals(1) = False
.PivotFields("開始日").Subtotals(1) = False
.PivotFields("オーダー数").Subtotals(1) = False
.PivotFields("親品番").Subtotals(1) = False
.ColumnGrand = False
.RowGrand = True
'ピボットテーブルにて挿入されたシート名を変数に代入
シート名 = ActiveSheet.Name
End With’←●ピボットテーブルが完成したここでWithを終わる

With Sheets(シート名)’←●追加
最終行 = .Cells(.Rows.Count, 1).End(xlUp).Row’●元の構文
Range(Range("B5"), Cells(最終行, 5)).Copy
Sheets("対象").Range("B4").PasteSpecial Paste:=xlPasteValues
Range(Range("F5"), Cells(最終行, 6)).Copy
Sheets("対象").Range("A4").PasteSpecial Paste:=xlPasteValues
Sheets("対象").Range("D1").Value = Left(着手日, 10)
Sheets("対象").Select
Range("A1").Select
End With
'ピボットテーブルのあるシートを削除
Application.DisplayAlerts = False
Sheets(シート名).Delete
Application.DisplayAlerts = True
Application.CutCopyMode = False
Sheets("手順").Select