エクセル マクロ実行時エラー’1004’

質問

For～Next構文内でシートコピーしていると途中で、
実行時エラー'1004’
WorksheetクラスのCopyメソッドが失敗しました。
が表示されることがあります。

該当のFor～Next文は10回以上実行されているので、コードの誤りではないと思うのですが、なぜ「Copyメソッドが失敗」するのかわかりません。
理由/対処方法がわかればありがたいと思い質問させていただきます。

**該当コード**
Sub 評価シート作成()
　　Sheets(社員一覧).Select
　　行 = 1
　　Do
　　　　ReDim Preserve 社員CD(行)
　　　　ReDim Preserve 氏名(行)
　　　　社員CD(行) = Cells(行 + 1, 1).Value
　　　　氏名(行) = Cells(行 + 1, 2).Value
　　　　行 = 行 + 1
　　Loop Until Cells(行, 1) = ""
　　人数 = 行 - 2
　　For 回数 = 1 To 人数
　　　　Sheets(評価シート).Select
　　　　Sheets(評価シート).Copy after:=Sheets(評価シート)
　　　　ActiveSheet.Name = 氏名(回数)
　　　　Cells(4, 5) = 氏名(回数)
　　　　Cells(4, 3) = 社員CD(回数)
　　Next 回数
End Sub
＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊
よろしくお願いします

回答No.3

pentium100 · ベストアンサー

ひょっとしてこれに該当するのかな？

Excel でプログラムを使用してワークシートをコピーするとランタイム エラー 1004 が発生する
http://support.microsoft.com/kb/210684/ja

お礼

テンプレートでの方法で無事対応できました。

補足

URL参考になります。
テンプレートでの方法を試してみようと思います。

回答No.4

Wendy02

こんにちは。

コードとしては、失礼ですが、動的配列を使いながら、2バイト文字変数では、ものすごくバランスが悪いです。申し訳ないのですが、私には、修正とかいうレベルではないです。2バイト文字の変数のオンパレードはミスの元です。動的配列を使いたい気持ちは分からないわけではないけれども、そのためには、もう少し、初歩的な技術の蓄積が必要ではないでしょうか。

もともと、そのコードでは動くわけないのでしょうから、掲示板に載せるために、相当に省いているのか加工しているのだと思います。

Sheets("評価シート").Copy after:=Sheets("評価シート")
"" のクォーテーションは、どうなっているのでしょうか？

ActiveSheet.Name = 氏名(回数)
これも、実にエラーが発生しやすいです。

それから、
For i = Rng.Rows.Count To 1 Step -1
左から順に入れていくなら、このように逆にしたほうがよいのではないでしょうか。これは一例です。

Sub EvaluationSheetMaking()
　　Dim Sh1 As Worksheet
　　Dim Sh2 As Worksheet
　　Dim Rng As Range
　　Dim i As Long

　　Set Sh1 = Worksheets("社員一覧")
　　Set Sh2 = Worksheets("評価シート")
　　With Sh1
　　　　Set Rng = .Range("A2", .Range("A65536").End(xlUp).Offset(, 1))
　　End With

　　For i = Rng.Rows.Count To 1 Step -1
　　　　On Error Resume Next
　　　　Sh2.Copy After:=Sh2
　　　　With ActiveSheet
　　　　　　.Name = Rng.Cells(i, 2).Value
　　　　　　.Cells(4, 5) = Rng.Cells(i, 2).Value
　　　　　　.Cells(4, 3) = Rng.Cells(i, 1).Value
　　　　End With
　　　　On Error GoTo 0
　　Next i
　　Set Rng = Nothing
End Sub

回答No.2

pentium100

http://oshiete1.goo.ne.jp/qa1822561.html
この辺が参考になるのかなー

回答No.1

pentium100

＞　　　ActiveSheet.Name = 氏名(回数)
のところが怪しい気がするんですが
実際はどの行でストップしてますか？
コードの該当行が反転しているはずですが

シート名に設定しようとしたデータ：氏名(回数)が空っぽとかではない？

補足

Sheets(評価シート).Copy after:=Sheets(評価シート で停止します。
何度か実行していますが同じ行数のところで失敗になります。
そうなるとワークシートから手作業でシートコピーしようとしてもできません。

一旦エクセルを終了→エクセルのみ再起動し、（シート名が重ならないよう）データを入替えて実行すれば、その前より少ない行数で失敗します。

なぜなのか見当もつきません。