こんにちはゲストさん。会員登録(無料)して質問・回答してみよう!

解決済みの質問

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)

投稿日時 - 2005-12-06 11:07:36

QNo.1822561

困ってます

質問者が選んだベストアンサー

コードとしては下記と等価だと思います。
空のシートでやってみると、スムーズにうまく行きました。
下記のシート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
エクセル2002でXP標準メモリ容量程度です。

投稿日時 - 2005-12-06 19:45:09

ANo.3

このQ&Aは役に立ちましたか?

4人が「このQ&Aが役に立った」と投票しています

回答(4)

ANo.4

こんばんは。

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

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

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

投稿日時 - 2005-12-07 00:17:34

ANo.2

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

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

投稿日時 - 2005-12-06 11:55:27

お礼

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

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

投稿日時 - 2005-12-06 13:03:33

ANo.1

こんにちは。KenKen_SP です。

自信なしですが、、、

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

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

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

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

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

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

投稿日時 - 2005-12-06 11:35:35

お礼

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

投稿日時 - 2005-12-06 12:49:58

あなたにオススメの質問