VBA ピボットテーブル自動更新

VBAでピポットテーブルの更新をしたいのですが、下記の記述で
３行目で「424エラー オブジェクトが必要です」となります。
意味がよくわかりません。
助けてください。よろしくお願いいたします。
[ソースデータ]シートを元データ（行範囲が可変）として
ピボットテーブル２を更新します。


Dim A As Range
Set A = Worksheets("ソースデータ").Range("A1").CurrentRegion
ActiveSheet.PivotTables(2).ChangePivotCache _ ActiveWorkbook.PivotCaches.Create(xlDatabase, A)

質問者からの補足コメント

• ありがとうございます。
  説明不足でした。ごめんなさい。
  
  元データのテーブル化も考えたんですが・・・
  外部から得た元データをシートごとコピペして同ブック内で
  ピボットテーブル更新処理していまして
  この場合はテーブル化が機能しないのではないかと
  勝手に思ってトライしていませんでした。
  
  ちなみにこの場合でも機能するのでしょうか？

  No.2の回答に寄せられた補足コメントです。 補足日時：2022/01/07 08:49

No.3 回答者： goomania 回答日時： 2022/01/07 16:04

No.2です。

>外部から得た元データをシートごとコピペして同ブック内で
>ピボットテーブル更新処理していまして
>この場合はテーブル化が機能しないのではないかと
>勝手に思ってトライしていませんでした

このような状態でもテーブル化そのものは問題なくできると思います。
ただ、前回回答で述べた通り、テーブル化→ピボットテーブルでは、「標準的な更新」においてテーブルのデータ部分の追加・削除を行えばテーブルが自動更新されるので、ピボットテーブルの更新での範囲の変更が不要となることもメリットのひとつです。
ここでいう「標準的な更新」とは、例えば前日までのテーブルから本日の処理による削除、本日の処理による追加を行ってメンテナンスし、これに基づきピボットテーブルを更新するイメージです。

ところが、ご質問者の説明だと「外部から得た元データをシートごとコピペして」とおっしゃっており、手続詳細は不明ですが、もしかすると、私の前回回答にある「データ更新があったら元表を破棄し、その都度VBAで作表している」という状態ではないかと推測します。

この状態だと、テーブルそのものが消失してしまう可能性が高く、テーブル化しても更新の都度テーブルからピボットテーブルの新規作成になってしまい、ピボットテーブルの更新の際のテーブル化のメリットが得られません。

あるいはテーブルを消去せずに、シートそのものを上書き貼り付けする手順なら、テーブルが1,048,576行目まで追加された状態(空白データあり)になるのかも知れません。多少荒業でよいのなら、この状態で、

ActiveSheet.PivotTables(1).PivotCache.Refresh

を実行すれば、ピボットテーブル自体は更新されます(ピボットの作成方法によっては行項目、列項目ともに「空白」が出現します)。
ただ、このような方法をとる方はいないのではないかと思います。

また、別の考え方としては元データのテーブルのタイトル部分だけ残してテーブルの「DataBodyRange」を消去して、外部から得た元データのデータ部分だけをテーブルに貼り付けるという方法ならテーブルが消失しないという考え方もできます。しかし、手順がより複雑になってしまいます。

あるいは、テーブルが消失しても同名のテーブルを再作成すればよい(つまり、ご質問者が外部データをコピペしたらその内容で同じ名前のテーブルを作ってしまえばよい)のではないかというアイディアもあります。
しかしこの場合、ピボットテーブルが望んだ形に更新されないように思います。(充分なテストを行っていません。あしからず。)

ということで、結論としては現在のご質問者の方法で考えるとfujillinさんがお示しになった方法が合理的ということになるのではないかと思います。

No.2 回答者： goomania 回答日時： 2022/01/06 22:47

ピボットテーブルを作成する場合、元データは、テーブルにしておくと都合がよいとされています。

テーブルから作成したピボットテーブルは、元データのデータ増減によるピボットテーブルの更新においてピボットテーブル側のデータ範囲を変更する必要がないからです。

つまり、ご質問のようなケースでは、そもそも範囲の変更をする必要がありません。

さらに、ピボットテーブルを作成するVBAを作成する場合も、テーブルを設定してから作成するとコードが短く簡素になると言われています。

>[ソースデータ]シートを元データ（行範囲が可変）として
>ピボットテーブル２を更新します。

とのことですが、行範囲が可変なのは、データ更新があったら元表を破棄し、その都度VBAで作表しているという意味でしょうか?

そうではなく、元の表に他のデータを転記するような手続きを行っているのなら、

Worksheets("ソースデータ").ListObjects.Add(Source:=Range("A1").CurrentRegion).Name = "テーブル1"

のように、まず元データをテーブルにしてから

ActiveWorkbook.PivotCaches.Create(xlDatabase,"テーブル1").CreatePivotTable Sheets.Add.Range("A3")

のようにピボットテーブルを作成するようにして、
行データを追加する場合は、n行、m列のテーブルなら

With Worksheets("ソースデータ").ListObjects(1)
n = .ListColumns(1).Range.Count
For i = 1 To m
.ListColumns(i).Range(n + 1) = data(i)
Next
End With

のようにテーブルに行データを追加するとテーブルが自動拡張するので、

ActiveSheet.PivotTables(1).PivotCache.Refresh

のようにピボットテーブルを更新すればよいことになり、ピボットテーブル側での範囲の変更は不要になると思うのですが･･･。

No.1 回答者： fujillin 回答日時： 2022/01/06 15:57

こんにちは

これの続きでしょうか？
https://oshiete.goo.ne.jp/qa/12745552.html
（同じ質問をするときは、その中で継続するかあるいは前の質問を閉じてからの方が宜しいかと）


さて、本題ですが・・
ご提示のままだと確かにエラーになるようですね。

作成したPivotCache を一旦変数においてから設定すれば動作するようです。
（直接代入しようとすると、なぜか、エラーになります）
　Set B = ActiveWorkbook.PivotCaches.Create(xlDatabase, A)
　ActiveSheet.PivotTables(2).ChangePivotCache (B)

一方で、前回も回答しましたように、SourceData を直接変更する方法でも行けると思います。
ActiveSheet.PivotTables(2).SourceData = "ソースデータ!" & A.Address(1, 1, xlR1C1)