【ExcelVBA】知見の深い方にお尋ねします。

とあるシートをfor文を用いて100枚コピーするマクロを組んだのですが、実行中に、コピー何回目かで「実行時エラー’1004’: ’Copy’メソッドは失敗しました」というエラーが出てきます。なお、これが一回出るともう一度同じマクロを実行してもすぐに同様のエラーが出ます。しかし保存して再起動するとまたマクロを実行することが出来ますが、同じようにコピー何回目かでエラーが出ます。
このエラーメッセージで検索をかけても有効な対処法が見つからないので、どなたか教えてください。
似たような事例:http://www.max.hi-ho.ne.jp/cgi-bin/user/happy/wforum.cgi?no=2462&reno=no&oya=2462&mode=msgview&page=0
なお、シート内のセルのコピーではなく、シートそのもののコピーをしたいのです。
回答は当該現象をご存知or確認できた方からの確実な情報を希望します。

回答の条件
  • URL必須
  • 1人2回まで
  • 登録:2005/03/16 20:49:16
  • 終了:--

回答(6件)

id:SigZ No.1

SigZ回答回数29ベストアンサー獲得回数02005/03/16 21:07:01

ポイント10pt

Excel2000で似たような現象を経験したことがあって、参照URLのように sheet の copy によってSheet名が sheet1111... となってしまうのが原因でした。

ご参考まで。

id:sexysaitama

参考情報は必要ではありません。

要件に沿った回答をお願いします。

なお、シート名はコピーと同時に変えています。

2005/03/16 21:11:24
id:taknt No.2

きゃづみぃ回答回数13539ベストアンサー獲得回数11982005/03/16 21:11:05

http://www.kotaete-net.net/bbs04007.aspx?intDlgMode=1&strQA_...

答えてねっと - メンテナンス中 -

シート数はメモリによるようなので、メモリによってコピーできる数が決まるようです。

マシンのメモリを増やしてみたらいかがでしょうか?

id:sexysaitama

あてずっぽうで回答はしないでください。確実な情報を希望しています。

様々なスペックのマシンで実験していますが、エラーの出るシートのコピー回数は同じです。

申し訳ないですが0ポイントです。

2005/03/16 21:16:21
id:akibare No.3

akibare回答回数157ベストアンサー獲得回数52005/03/17 08:00:14

ポイント10pt

http://support.microsoft.com/default.aspx?scid=kb;en-us;1691...

XL97: Run-Time Error in Macro Assigned to Control

Excelのバージョンおよびコードを見せていただければより助かりますが。。

(1)Excel97で、ボタンなどのコントロールからマクロをキックしている場合はフォーカスをはずすと良いそうです。

http://support.microsoft.com/default.aspx?scid=kb;en-us;2106...

Copying worksheet programmatically causes run-time error 1004 in Excel

(2)Excel2000では、大量にコピーする場合は定期的に保存・閉じる・開くを実行することにより問題を回避できるそうです。

最後にMSのナレッジベースの検索結果(全部は見ていないので)

id:sexysaitama

すみません、肝心のバージョンを書き忘れていました。

バージョンはexcel2003ですが、2002でも2000でも動くようにしたいです。

ソースは下記になります。

***************

For I = 2 To 17

Worksheets(”シート_1”).copy After:=Worksheets(Worksheets.Count)

ActiveSheet.Name = ”シート_” & I

Next I

****************

なぜかいつもコピー28回目で質問文に記載したエラーがおきます。

なお、コピー元のシートを編集して中身をいじると、エラーが出るまでのコピー回数が変わります。

途中の保存・再起動やセルのコピーといった代替手段を使いたくないのです。

よろしくお願いします。

2005/03/17 08:33:56
id:akibare No.4

akibare回答回数157ベストアンサー獲得回数52005/03/17 08:54:07

ポイント40pt

実験して再現しました。コピーした新しいシートの「後」にコピーを挿入するとエラーになるようです(なんとなくWorksheetsのインデックスがメモリー上更新されていないとかそんな感じ)。下記の通りにすれば問題は起きませんので試してみてください:

Sub CopySheets()

Dim ws, cp As Worksheet

Set ws = ActiveWorkbook.Worksheets(”Sheet1”)

For i = 1 To 200

ws.Copy After:=ws

ActiveSheet.Name = ”Copy” & (201 - i)

Next

End Sub

id:sexysaitama

ありがとうございます。

実践してみたところ、33回目(以前より増えましたが)でやはり同じエラーが出てきてしまいました。デバッグでフォーカスされてるのは

ws.copy After:=ws

の部分でした。なんでだろう!?!

2005/03/17 13:45:29
id:denbee No.5

denbee回答回数1ベストアンサー獲得回数02005/03/17 13:54:30

ポイント10pt

URLはダミーです。

当方の環境でも再現しました。

当初は発生しなかったのですが、シートにオブジェクトを増やしたところ問題文のエラーメッセージが出ました。

まずはオリジナルとなるシートのデータ量を減らすこと(セルのデータ、シート上のオブジェクト)、およびウィンドウズの仮想メモリなどの設定を見直すことをお勧めします。

id:sexysaitama

シートの容量は減らせません。

頒布用のexcelブックなので、excel以外の設定を見直すことはできませんし、OSやバージョンに依存しない方法が知りたいです。

代替手段なしでお願いします。

2005/03/17 18:50:32
id:Nikki_A No.6

にっき回答回数32ベストアンサー獲得回数02005/03/17 17:55:29

ポイント20pt

技術的な回答ではないので、ポイントはいりません。

3番目の答えについていたコメントのサンプル、4番目の答えのサンプル、どちらも私の環境ではエラーが出ないのです。

2番目の方と同じく、私もこのエラーにはシステムやメモリが関係しているような気がしてなりません。

参考までに、こちらの実験環境を書きます。

WindowsXP Professional SP2

Excel2003

メモリ:1GB CPU:2.8MHz

コピーしたのはA1〜L108まで

文字や罫線が入り混じったシートです。

実行したマクロは以下のとおりです。

Sub CopySheets()

Dim ws, cp As Worksheet

Set ws = ActiveWorkbook.Worksheets(”Sheet1”)

For i = 1 To 500

ws.Copy After:=ws

ActiveSheet.Name = ”Copy” & i

Next

End Sub

id:sexysaitama

システムやメモリが関係しているとなれば、

システムやメモリに依存しない形でシートのコピーでエラーを出さない方法は無いってことになりますね。。(´・ω・`)

社内のどのマシーンでも動くようにしたいのですが、ちょっと無理そうですね。

とりあえずここで締め切ります。実験してくださった方々、ありがとうございました。

2005/03/17 18:56:13
  • id:akibare
    書き忘れましたが

    コピー元のシートに名前が定義されているCell/Rangeが存在するときだけ再現します。
    それから、[定期的に保存・もう一度開く]というのも全部スクリプト内でやるのでユーザー側から見たら特に気が付かないのでは?
  • id:taknt
    Re:書き忘れましたが

    >コピー元のシートに名前が定義されているCell/Rangeが存在するときだけ再現します。
    >それから、[定期的に保存・もう一度開く]というのも全部スクリプト内でやるのでユーザー側から見たら特に気が付かないのでは?

    新規のシートをただ単純にVBAでコピーさせていくだけで 途中でエラーが発生します。
    私が 試した環境では 900ちょいまでいきました。
    別の環境では 1300ぐらいまでいくようです。

    >[定期的に保存・もう一度開く]
    というのは確実なんでしょうかねぇ。
    時間があれば試してみたいのですが、マシンのスペックが低いのでなかなか確認できないですね。

この質問への反応(ブックマークコメント)

トラックバック

「あの人に答えてほしい」「この質問はあの人が答えられそう」というときに、回答リクエストを送ってみてましょう。

これ以上回答リクエストを送信することはできません。制限について

絞り込み :
はてなココの「ともだち」を表示します。
回答リクエストを送信したユーザーはいません