データを書きだす→範囲を指定してピボットテーブル

質問

「データを書きだす→範囲を指定してピボットテーブルを作成する」
と言う二つのプロシージャーをいっきにやってるのですが、
二つに区切って実行すると問題ないのですが、一気に実行すると実行時エラーが発生します。

-------------------------------------------------------------
Sub tset()
Call 全てのファイル名と作成日時を取得する
Call ピボテを作成する
End Sub
Sub ピボテを作成する()
ActiveWorkbook.Names.Add Name:="範囲の名前", RefersToR1C1:="=Sheet1!R1C1:INDEX(Sheet1!C2,COUNTA(Sheet1!C1))"

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

End Sub
Sub 全てのファイル名と作成日時を取得する()
Dim MyRow As Integer
Dim MyFileName As String
Dim MyFolderName As String

Workbooks.Add
Cells(1, 1) = "ファイル名"
Cells(1, 2) = "作成日時"
MyRow = 2

MyFolderName = "C:\Users\Documents"

MyFileName = Dir(MyFolderName & "\*.*")
Do While MyFileName <> ""
Cells(MyRow, 1) = MyFileName
Cells(MyRow, 2) = CreateObject("scripting.filesystemobject").GetFile(MyFolderName & "\" & MyFileName).DateCreated
MyFileName = Dir()
MyRow = MyRow + 1
Loop
End Sub
-------------------------------------------------------------

このコート度実行すると
ActiveWorkbook.PivotCaches.Add(SourceType:=xlDatabase, SourceData:="範囲の名前"). _
で、
実行時エラー 1004 "指定したシートには既に同じ名前のピボットテーブル　レポートが存在します"
になります。

しかし、Workbooks.Addをやっているので、上記のエラーはありえないはずです。
そしてこの実行時エラーが出た後は、デバッグ→Ｆ５で、最後まで問題なく実行されます。

エクセルのバージョンは２００３です。2007でも試しましたが同じ結果でした。
エラーを無視以外での対処方法をご教授ください。

回答No.1

MarcoRossiItaly · ベストアンサー

お示しのコードでは、

●名前を付ける範囲の参照が相対参照になっています。ブックの使い方にもよるのかもしれませんが、「$」がなくても大丈夫ですか。
●「Workbooks.Add」により作製されるブックがアクティブになり、SourceData も、このブックから取っているようです。
　また、「TableDestination:=""」と書いてある箇所があります。
　「Workbooks("ファイル名").Activate」とか、「Worksheets("シート名").Activate」みたいな記述は入れなくてもよいのでしょうか。

参考 URL の例は、1 ファイル内で完結している処理です。「Sheets.Add」を記述することでそのシートがアクティブになっています。

● CreatePivotTable メソッドと PivotTableWizard メソッドが併記されています。
　同じシート上に両者がピボットテーブルを作ることになるので、そのエラーメッセージが出ているのだと思います。
　片方の記述のみを残し、他方はプロシージャから削除します。
　両者はどちらもピボットテーブルを作りますが、どちらを選ぶかは概ね、好みの問題のようです。
　後者では OLE DB というアクセス方法によるデータが使えないとヘルプにあるくらいで、致命的な差はないようです。

参考URL：

http://www.relief.jp/itnote/archives/018038.php

お礼

ありがとうございます。