Excel で、プログラムを介してワークシートをコピーすると「実行時エラー 1004 」が発生する

適用対象: Microsoft Office Excel 2007Microsoft Office Excel 2003Excel 2010

現象


Microsoft excel で、ワークシートをコピーして発生元である同じブックにそのワークシートを配置するマクロを実行します。 この場合、次のいずれかのエラー メッセージに似たエラー メッセージが表示されることがあります。

実行時エラー '1004':
ワークシート クラスのコピー メソッドが失敗しました。
実行時エラー '1004':
アプリケーション定義またはオブジェクト定義のエラー

原因


マイクロソフトでは解説することのみを目的としてプログラミング例を提供しています。暗示あるいは明示していることに対しての保証はしておりません。ここで言う保証とは、特定の目的に対する商品性や適合性の暗示的保証を含んでいますが、それに限定されるわけではありません。この資料では、例示されているプログラミング言語およびプロシージャの作成やデバッグに使用するツールにお客様が精通していることを前提としています。マイクロソフトのサポート エンジニアは、特定のプロシージャの機能について説明することができます。ただし、お客様固有の要件を満たすために、追加機能の提供またはプロシージャの作成のために、これらの例に変更を加えたりはしません。

この問題は、次のサンプル コードに示すように、ワークブックに定義名をつけ、最初にワークブックを保存も閉じもしないでワークシートを何度もコピーをすると発生します。


Sub CopySheetTest()
Dim iTemp As Integer
Dim oBook As Workbook
Dim iCounter As Integer

' Create a new blank workbook:
iTemp = Application.SheetsInNewWorkbook
Application.SheetsInNewWorkbook = 1
Set oBook = Application.Workbooks.Add
Application.SheetsInNewWorkbook = iTemp

' Add a defined name to the workbook
' that RefersTo a range:
oBook.Names.Add Name:="tempRange", _
RefersTo:="=Sheet1!$A$1"

' Save the workbook:
oBook.SaveAs "c:\test2.xls"

' Copy the sheet in a loop. Eventually,
' you get error 1004: Copy Method of
' Worksheet class failed.
For iCounter = 1 To 275
oBook.Worksheets(1).Copy After:=oBook.Worksheets(1)
Next
End Sub

解決策


この問題を解決するには、次のサンプル コードに示すように、コピー処理が行われている間に、定期的にブックを保存して閉じます。
Sub CopySheetTest()
Dim iTemp As Integer
Dim oBook As Workbook
Dim iCounter As Integer

' Create a new blank workbook:
iTemp = Application.SheetsInNewWorkbook
Application.SheetsInNewWorkbook = 1
Set oBook = Application.Workbooks.Add
Application.SheetsInNewWorkbook = iTemp

' Add a defined name to the workbook
' that RefersTo a range:
oBook.Names.Add Name:="tempRange", _
RefersTo:="=Sheet1!$A$1"

' Save the workbook:
oBook.SaveAs "c:\test2.xls"

' Copy the sheet in a loop. Eventually,
' you get error 1004: Copy Method of
' Worksheet class failed.
For iCounter = 1 To 275
oBook.Worksheets(1).Copy After:=oBook.Worksheets(1)
'Uncomment this code for the workaround:
'Save, close, and reopen after every 100 iterations:
If iCounter Mod 100 = 0 Then
oBook.Close SaveChanges:=True
Set oBook = Nothing
Set oBook = Application.Workbooks.Open("c:\test2.xls")
End If
Next
End Sub
注: ブックを保存する必要が出てくる前にワークシートをコピーできる回数は、ワークシートのサイズによって異なります。

回避策


この問題を回避するには、既存のワークシートをコピーする代わりに、テンプレートから新しいワークシートを挿入します。これを行うには、実行している Excel のバージョンに応じて以下の手順に従います。

Microsoft Office Excel 2007

  1. 新しいブックを作成し、次にワークシート 1 つを除いてすべてのワークシートを削除します。
  2. ブックの書式を設定し、テンプレートにデフォルトで任意のテキスト、データ、および必要なグラフを追加します。
  3. Microsoft Office ボタンをクリックし、[名前を付けて保存] をクリックします。
  4. [ファイル名] ボックスに、Excel テンプレートに使用する名前を入力します。
  5. [ファイルの種類] リストの一覧で、[テンプレート (*.xltx)] をクリックし、[保存] をクリックします。
  6. テンプレートをプログラムを使って挿入するには、次のコードを使用します。
    Sheets.Add Type:=path\filename


    このコードでは、パス\ファイル名では、シート テンプレートの完全パスとファイル名を含む文字列です。

Microsoft Office Excel 2003 およびそれ以前のバージョンの Excel

  1. 新しいブックを作成し、次にワークシート 1 つを除いてすべてのワークシートを削除します。
  2. ブックの書式を設定し、テンプレートにデフォルトで任意のテキスト、データ、および必要なグラフを追加します。
  3. ファイルをクリックし、[名前を付けて保存] をクリックします。
  4. [ファイル名] ボックスに、Excel テンプレートに使用する名前を入力します。
  5. [ファイルの種類] リストの一覧で、[テンプレート (*.xlt)] をクリックし、[保存] をクリックします。
  6. テンプレートをプログラムを使って挿入するには、次のコードを使用します。
    Sheets.Add Type:=path\filename


    このコードでは、パス\ファイル名では、シート テンプレートの完全パスとファイル名を含む文字列です。

状況


マイクロソフトは、この問題を「対象製品」セクションに記載されているマイクロソフト製品の問題として認識しています。