VBA コピペの途中でエラーになってしまいます。

質問

以前、質問させて頂き、マクロでしたい事の記述方法を教えて頂きました。
ご教示頂いたマクロ記述に更に手を加えて、下記のように記述しました。

したいことは、一つのフォルダにExcel Bookが１２０ほどあり、その中のマクロを組んだ集計用Book以外のBookから同じ名前のシート”結果”をコピーして、コピペされたシートは１、２、３・・という名前にして集計用Bookに値貼り付けをする。。というものです。
ところが、下記のマクロを実行するとシート名25までコピペされるのですが、途中で
”問題が発生したため MICROSOFT OFFICE EXCELを 終了します。・・・・・”　とエラーになってしまいます。
このエラーを回避して120ほどあるシートをマクロのある集計Bookへコピペするには、どのようにしたら良いでしょうか？
ご存知の方がみえたら、ご教示下さい。宜しくお願いいたします。

＜マクロの記述＞
Sub macro()
Const Aフォルダ As String = "C:\Documents and Settings\Bic\デスクトップ\Aフォルダ\"
Dim FileCounter As Integer
Dim myName As String

myName = Dir(Aフォルダ & "*.xls")
FileCounter = 0
Application.ScreenUpdating = False

Do While myName <> ""
If myName <> ThisWorkbook.Name Then
Application.DisplayAlerts = False
Workbooks.Open Aフォルダ & myName
On Error Resume Next
Workbooks(myName).Worksheets("結果").Copy After:=ThisWorkbook.Worksheets(2 + FileCounter)
If Err.Number = 0 Then
ActiveSheet.Range("A1:L35").Copy
ActiveSheet.Range("A1:L35").PasteSpecial Paste:=xlPasteValues
Application.CutCopyMode = False
FileCounter = FileCounter + 1
ActiveSheet.Name = FileCounter
End If
On Error GoTo 0
Workbooks(myName).Close
End If
myName = Dir
Application.DisplayAlerts = True
Loop
Application.ScreenUpdating = True

End Sub

回答No.2

yuki-alpha · ベストアンサー

こんばんわ。

■文中に対する回答
>Resume　Nextは、最初にマクロの記述を教えて頂いたとおり試してみたところ、何度かエラー表示されたので、回避しようと記述しました。。
「何度かエラー」とは具体的に何なのか、最初にそこの所を詳しく書くと良かったと思います。
そこでエラーを無理やり押さえ込んでも、そのツケは必ずどこかに回ってきます。
なので、そこはエラーの原因をちゃんと追究すべき場面ですよ。

>すると　ActiveSheet.CutCopyMode = False　に［実行時エラーー438：オブジェクトはこのプロパティ、またはメソッドをサポートしていません］というエラーが出ることがわかりました。
ActiveSheet.CutCopyModeですか？
Application.CutCopyModeじゃなくて？
イミディエイトで「ActiveSheet.CutCopyMode = False」を実行しましたが、やはりエラーになりますね。誤記ではないのですか？
でも、最初の投稿にあったソースコードにはApplicationで書いてあるし…どっちが正しいんですか？

■チェックすべき点
(1)ファイルメニューの＜デバッグ＞→＜[プロジェクト名]のコンパイル＞がちゃんと通るか。（文法的に間違いが無いか）
(2)落ちる直前に開いていたワークシートに問題はないか？
(1)ブックにパスが掛かっていないか
(2)ワークシートが保護されていないか
(3)非表示のワークシートを参照していないか
(4)手でちゃんと開けるか
(3)ActiveSheetが自分の意図したワークシートを指しているのか？
⇒ご存知かもしれませんが、デバッグ中に他のワークシートを変更しても、そこがActiveSheetとして参照されます。
やり方は何でもいいですが、Debug.Print ActiveSheet.nameなどで、あなたが意図したとおりのシートを参照しているかチェックしてください。特に20シート目以降。

■ソースコードを見て思ったこと
(1)デバッグ中はOn Error Resume Next や、Application.DisplayAlerts、Application.ScreenUpdatingは、無効にしましょう。エラーや警告を見落とします。エラーが無く、意図通り動作することが分ったらことが分ったらONに。
(2)25シートまでは順調にコピペの動作をしてくれるなら、その状態で26シート目を手でペーストするとどうなるのか？
(3)そもそも、１２０シートもブックに保存できるのか？空シートなら100枚でも余裕らしいけど、データありのシートだと40枚程度が限界らしい。実現方式に問題は無いのか？
[エクセル2003で最大シート数は？]
http://okwave.jp/qa/q3047947.html

お礼

yuki-alphaさん、丁寧に色々ご教示下さり、本当にありがとうございました。

昨日、今日ににかけてyuki-alphaさんのチェックすべき点を全部チェックしたり、120シートのコピペは本当に出来ないかなど確認していました。
確認してわかったのは、120シートコピペ出来ました。
ただ、コピー元の120のBookの内容？容量？が軽い場合です。
私が実際に使うコピー元の120のBookは複雑なマクロも組まれており、また１つのBookに８シートあり、すべてのシートに関数が組まれています。
テスト用の120のBookも同じように簡単な関数を使った表を８シート、簡単なマクロを組んだものをテスト用に作り、そこで試してみたら120コピペ出来ました。

たぶん、コピー元のマクロに原因がありそうかな。。と。
しかし、コピー元のマクロはコピー元Bookの情報を取得する為、絶対触ってはいけないとのことなので、仕方なく120のBookを20～25に分けて、シートのコピペをして、集計Bookを更に集計します。とりあえず、その方法でも手動でするよりは早いので（uu;）

それからyuki-alphaさんのご指摘通り、恥ずかしながら色々試しているうちに記述を書き間違えてました(><)!（Application.CutCopyMode = False でした。）

色々丁寧にご教示下さり本当にありがとうございました。

■ソースコードを見て思ったことの（１）や、■チェックすべき点など。。今後デバックが出る前、出た時に役立つ情報をありがとうございました！

回答No.1

yuki-alpha

これ、Application.ScreenUpdating を True のままで、
On Error Resume Nextをコメントアウトした状態でマクロを実行したら何らかの実行時エラーが出てきませんでしたか？
Resume Nextは「エラーを無視して先に進む」なので、重要なエラー情報を見落とす可能性があります。ご存知かもしれませんが。なにか、エラーを抑止するしかない事情が？
もしかしたら、Excelが落ちる現象も、そのへんで起こっているかもしれませんね。
で、もし実行時エラーが出ていた場合、そのメッセージも書いていただけると、より正確な回答が得られるのではないでしょうか。
あと、ステップ実行してどの命令で落ちているか、など。

お礼

早速のご回答、本当にありがとうございます。
まだVBAを学び出したばかりで、質問の仕方も下手で申し訳ありません。

Resume　Nextは、最初にマクロの記述を教えて頂いたとおり試してみたところ、何度かエラー表示されたので、回避しようと記述しました。。が、
yuki-alphaさんのご提案のように、一度コメントアウトし、実行してみました。
すると　ActiveSheet.CutCopyMode = False　に［実行時エラーー438：オブジェクトはこのプロパティ、またはメソッドをサポートしていません］というエラーが出ることがわかりました。
そこで、ActiveSheet.CutCopyMode = False　をコメントアウトして再度実行しましたが、最初のエラーと変わらず、25シートまではコピペされるのですが、［問題が発生したため、Microsoft Office Excelを終了します。ご不便をおかけして申し訳ありません。］というエラーになってしまいました。
ActiveSheet.CutCopyMode = False　以外のメソッドはステップ実行をして確認をしたのですが、特にエラーもなく、25シートまでは順調にコピペの動作をしてくれます。

もし、まだ何か回避できるような案があれば、是非是非ご教示下さい。
よろしくお願い致します。