VBA - 別のワークブックにピボットテーブルを作るマクロ

Question

ワークブック1にあるデータについて、PivotCacheを作成し、
別のワークブック2にそのPivotTableを作成したいのですが、★でエラーになってしまいます。

引数TableDestinationには、元データのあるワークブックオブジェクトしか参照できないのでしょうか。
調べてもそれらしいやり方がわからず・・・他のやり方があれば是非ご教示頂きたく。

Sub test()

Dim wb As Workbook
Dim new_wb As Workbook
Dim ws1 As Worksheet

Set wb = Workbooks("ワークブック1.xlsm")
Set ws1 = wb.Worksheets("シート1")

'ピボットキャッシュを作成
Dim pvCacheObj As PivotCache
Set pvCacheObj = wb.PivotCaches.Create(SourceType:=xlDatabase, SourceData:=ws1.Range("A1").CurrentRegion)

'新規ワークブックを作成
Set new_wb = Workbooks.Add
ActiveSheet.Name = "シート2"
new_wb.SaveAs "ワークブック2"

'ピボットテーブルを作成
pvCacheObj.CreatePivotTable TableDestination:=new_wb.Sheets("シート2").Range("A1"), TableName:="ピボットテーブル" ★

End Sub

実行時エラー'5'
プロシージャの呼び出し、または引数が不正です。

Answer 1

PivotCacheが、ワークブック別に管理されているそうなので、手順を入れ替えましょう。

CreatePivotTableメソッドについて調べてみる

ワークブック2を作成してから、ワークブック2にPivotCacheを作成してみてください。

Comments

[koyamashinji]

ご助言有難うございました。
ワークブック2を作成してから、ワークブック2にPivotCacheを作成したところ、正常に動きました。

[YT0014]

解決おめでとうございます。お役に立てたのでしたら、何よりです。

Answer 2

YT0014様の仰るとおり新しいワークブックを用意する→新しいワークブック上にピボットキャッシュを作る、というふうに順番を入れ替えると、正常動作しました。

Sub test()

Dim wb As Workbook
Dim new_wb As Workbook
Dim ws1 As Worksheet

Set wb = Workbooks("ワークブック1.xlsm")
Set ws1 = wb.Worksheets("シート1")

'新規ワークブックを作成
Set new_wb = Workbooks.Add
ActiveSheet.Name = "シート2"
new_wb.SaveAs "ワークブック2"

'ピボットキャッシュを作成
Dim pvCacheObj As PivotCache
Set pvCacheObj = new_wb.PivotCaches.Create(SourceType:=xlDatabase, SourceData:=ws1.Range("A1").CurrentRegion)

'ピボットテーブルを作成
pvCacheObj.CreatePivotTable TableDestination:=new_wb.Sheets("シート2").Range("A1"), TableName:="ピボットテーブル"

End Sub