EXCEL VBA シートをコピーする時にエラーが発生してしまう件

質問

EXCEL VBAでシートをコピーするマクロを作成しているのですが、【10】にて時々エラーが発生してしまいます。100シート分ぐらいをコピーしたいのですが、途中で30シートぐらいの所で止まってしまいます。
（シートが10前後だと問題なく処理が終ります）
止まってしまうシートはまちまちなので、
シート名称が問題だとは考えにくいです。

ちなみに【8】でのsheets_nameは取得できてます。
前後に何かを入れることにより解決するのか、
ソース本体をいじれば解決するのか、

詳しい方、何卒、解決方法を
ご指導いただけますよう宜しくお願いします。

【1】 '取得した配列の数だけループを行う。
【2】 For i = LBound(test) To 配列数 - 1
【3】
【4】 列番号 = Mid(test(i), 4, 3)
【5】 Debug.Print "test(" & i; ") : " & test(i)

【6】
【7】 'FORMATシートをコピーし、その名称を変数名にする
【8】 sheets_name = Worksheets("実績").Cells(18, 列番号).Value
【9】
【10】 Worksheets("FORMAT").Copy After:=Sheets("FORMAT")
【11】
【12】 ActiveSheet.Name = (sheets_name)
【13】 Cells(4, 3) = (sheets_name)

回答No.3

imogasi · ベストアンサー

コードとしては下記と等価だと思います。
空のシートでやってみると、スムーズにうまく行きました。
下記のシートSheet1のデータ・関数式他メモリの使用状況によるのではないでしょうか。
コピーの終了を見届けて、次のループに入ればよいのかもしれませんが、可能か、どうすればよいか方法が私にはわかりません。
Sub test02()
For i = 1 To 100
sheets_name = i
Worksheets("Sheet1").Copy After:=Sheets("sheet1")
ActiveSheet.Name = sheets_name
Next i
End Sub
エクセル２００２でXP標準メモリ容量程度です。

回答No.4

Wendy02

こんばんは。

シートコピーは、無限にできるわけでもないので、

＞途中で30シートぐらいの所で止まってしまいます。

このぐらいが、常識的な限度だと私は思います。ワークシートの内容量にもよりますが、この常識的な量は超えないほうが安全だと思います。実験的には、何百シートができるだとしても、私は、量が増える可能性がある場合は、ブックで分散して、それを参照設定でつないでいます。

回答No.2

hana-hana3

コピー方法を変えると可能です。

http://oshiete1.goo.ne.jp/kotaeru.php3?q=1685998
http://oshiete1.goo.ne.jp/kotaeru.php3?q=1728889

お礼

この質問は非常に参考になりました。
http://oshiete1.goo.ne.jp/kotaeru.php3?q=1685998

このサイトでの事前の調べが足りなかったです（反省）
ありがとうございました。
解決に繋がりそうです。

回答No.1

KenKen_SP

こんにちは。KenKen_SP です。

自信なしですが、、、

「たまにうまくいかない」ということですね？

連続コピーで Excel がビジーになり、処理が追いつかないのではないか
と推測します。適度にウェイトしてみてはどうでしょうか？

標準モジュールの先頭に以下の API 関数を貼り付けます。

Declare Sub Sleep Lib "kernel32" (ByVal dwMilliseconds As Long)

それから、ご提示のコードを次を追加します。

Worksheets("FORMAT").Copy After:=Sheets("FORMAT")
Sleep 500 '<-- 追加：500ミリ秒のウェイト（任意）
DoEvents '<-- 追加：制御を一瞬 OS に渡します

お礼

KenKen_SPさん、
ありがとうございました。
早速、試してみます。
ウェイトをどうさせたらいいのかな～？
と思っていたのでズバリでした。
本当にありがとうございました。