excelマクロの記録：実行時エラー5の解決方法

前提・実現したいこと

Excelでのルーティン作業（ピボットテーブルの作成）を、マクロ記録・実行して次回から自動化したいと考えています。
一通りマクロの記録を行い、実行したところ以下のエラーメッセージが発生しました。

発生している問題・エラーメッセージ

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

該当のソースコード

下記の「AActiveWorkbook.PivotCaches.Create(SourceType:=xlDatabase,～DefaultVersion:=6」の部分が黄色くハイライトされて実行が止まります。

Sub ピボットＡＢＣ更新作業()
'
' ピボットＡＢＣ更新 Macro
'

'
    Sheets("ABCDetail").Select
    Sheets.Add
    ActiveWorkbook.PivotCaches.Create(SourceType:=xlDatabase, SourceData:= _
        "Table_ABCDetail", Version:=6).CreatePivotTable TableDestination _
        :="Sheet2!R3C1", TableName:="ピボットテーブル2", DefaultVersion:=6

試したこと

記録時は、シート「ABCDetail」のタブをクリック→挿入からピボットテーブルの作成を選択して、新規シートにピボットテーブルが作成されるようにしました。

※ルーティン作業で毎回「ABCDetail」というexcelシートから、新しくピボットテーブルを新規シートに作成していおり、この作業を自動化したいと考えています。

補足情報

Excel2016使用しています。

エラーの解決方法がさっぱりの為、アドバイスいただければ幸いです。
よろしくお願いいたします。

行動規範の内容に同意します

回答2件

ベストアンサー

マクロの記録ではその時作成されたシートの場所を固定（"Sheet2!R3C1"）で指定しているので、
このマクロを再度実行すると、シートは新規で追加されるが、ピポットテーブルを作成する場所は、
マクロの記録時に作られたシートになり、ピポットテーブルが重なることができないことによりエラーになります。

以下のようにしてはどうでしょうか。

VBA
1    On Error Resume Next
2    Worksheets("シート名").Select
3    'Application.DisplayAlerts = False
4    ActiveSheet.Delete
5    Application.DisplayAlerts = True
6    On Error GoTo 0
7    Sheets.Add
8    ActiveSheet.Name = "シート名"
9
10    ActiveWorkbook.PivotCaches.Create(SourceType:=xlDatabase, SourceData:= _
11        "Table_ABCDetail", Version:=6).CreatePivotTable TableDestination _
12        :="シート名!R3C1", TableName:="ピボットテーブル2", DefaultVersion:=6

投稿2018/03/20 07:58

編集2018/03/23 02:50

sazi

総合スコア25206

退会済みユーザー

2018/03/23 02:23

早速のアドバイスありがとうございます！マクロの仕組みについてわかりやすくご解説いただき、理解できました。具体的なコードもいただきありがとうございます！コードの内容の理解に時間がかかり、すっかりお礼が遅くなってしまい恐縮です。実行してみましたところ、現在のところ、「実行時エラー1004:この名前は既に使用されています。別の名前を入力してください。」と表示され、 ActiveSheet.Name = "シート名" が黄色くなりました。そこで、「シート名」を「Sheet3」としてみましたところ、「実行時エラー1004:参照が正しくありません。」となってしまいました。コードの理解ができてない為か、なかなか自力で解決できず、もしよろしければ、追加ヒントいただければ幸いです。よろしくお願いいたします。

sazi

2018/03/23 03:04 編集

シート名は既存の名前と被らなければ、自由に命名して貰って構いません。シートを追加するので、事前に削除としていますが、存在していない場合があるので、 On Error Resume Next　でその場合はスルーするようにしています。削除の場合には確認メッセージが出力されます。実際に行う場合には、確認メッセージが出力されない方が良いと思うので、 Application.DisplayAlerts の制御を追記しておきました。確認が取れれば、コメントを外してもらえば、メッセージは出力されません。シートの追加については、Sheets.Addで内部的に管理しているシート名(SheetXX)で追加されます。追加されたシートがアクティブになっているので、そのアクティブシートの名前（ActiveSheet.Name）を変更しています。

sazi

2018/03/23 02:58

>「実行時エラー1004:この名前は既に使用されています。別の名前を入力してください。」が出力されるということは、削除するシート名と追加するシート名が違うコーディングとなっていないでしょうか？ >「実行時エラー1004:参照が正しくありません。」このエラーが発生するのは、ActiveWorkbook.PivotCaches.Create　の部分ではないでしょうか？一度、サンプルで言うところの「シート名」の箇所を見直して見てください。

sazi

2018/03/23 03:08

一応手元で確認したコードをサンプルとしています。ピポットテーブルの追加のコード部分はエクセルのバージョンが違い一部異なっていますので、提示されたコードのシート名部分を合わせたものにしています。

退会済みユーザー

2018/04/04 03:29

ご丁寧なフォローアップ誠にありがとうございました！理解に時間がかかり恐縮でした。繰り返しアドバイス読ませていただきながら、コードを確認していましたら、ついにやっとエラーが出なくなり、いただいたアドバイスの意味もクリアに理解できるようになりました！！わかりやすくご解説いただき、ありがとうございました！大変助かりました！！

行動規範の内容に同意します