アプリケーション定義または、オフジェクトエラー

質問

office2010

マクロ実行で”アプリケーション定義または、オフジェクトエラー"が発生します
いろいろ調べてみたのですが、原因分からず、教えてください。
Sheet1のA1～A8までシート名が記載されています。
このシート名の後ろにdataを付与してシートを追加し、編集用とするマクロです。
Macro13を実行してMacro1のエラー発生という所が黄色くなります。
' Range("E1:S" & Worksheets(Worksheets("SHEET1").Cells(i, 1).Value).Range("B" & Rows.Count).End(xlUp).Row).Value = "=A1管理!RC[-4]"
の場合は、動作します。
なので、
Worksheets(""SHEET1"").Cells(i, 1).value!
が悪いと思うのですが、どうしたらよいかが分かりません。

Dim i As Long
Sub Macro13()
'
Sheets("Sheet1").Select

For i = 1 To Range("A1").End(xlDown).Row

With Worksheets.Add()

.Name = Worksheets("SHEET1").Cells(i, 1) & "data"

Call Macro1

End With

Next

End Sub
Sub Macro1()
' 各シートの整列版作成
'
'データ参照

' Range("E1:S" & Worksheets(Worksheets("SHEET1").Cells(i, 1).Value).Range("B" & Rows.Count).End(xlUp).Row).Value = "=A1管理!RC[-4]" '動作Ok
Range("E1:S" & Worksheets(Worksheets("SHEET1").Cells(i, 1).Value).Range("B" & Rows.Count).End(xlUp).Row).Value = "=Worksheets(""SHEET1"").Cells(i, 1).value!RC[-4]"　’エラー発生

'下記は編集内容
'D列：F列を参照し、先頭６桁の右３文字がドライだったらTD、先頭６桁の右３文字がレンチだったらTL

Range("D1:D" & Range("E" & Rows.Count).End(xlUp).Row).Value = "=IF(RIGHT(LEFT(RC[2],6),3)=""ドライ"",""TD"",IF(RIGHT(LEFT(RC[2],6),3)=""レンチ"",""TL"",""""))"

'計算用に１行ダミー行挿入

Range("A1").Select
Selection.EntireRow.Insert

'C列：E列を参照し、セル結合されているセルを名称、フロント、リアの区分名とする

Range("C2:C" & Range("E" & Rows.Count).End(xlUp).Row).Value = "=IF(RC[2] <>0,RC[2],R[-1]C)"

'B列：E列を参照し、先頭１桁がNだったら名称、0だったらＢ列1つ上の名称

Range("B2:B" & Range("E" & Rows.Count).End(xlUp).Row).Value = "=IF(LEFT(RC[3],1)=""N"",RC[3],R[-1]C)"

'A列：B,C,D列を結合
Range("A2:A" & Range("E" & Rows.Count).End(xlUp).Row).Value = "=IF(RC[1]=RC[2],"""",CONCATENATE(RC[1],RC[2],RC[3]))"

End Sub

回答No.1

tsubu-yuki · ベストアンサー

あー、なるほど。

　　　　Range("E1:S" & Worksheets(Worksheets("SHEET1").Cells(i, 1).Value).Range("B" & Rows.Count).End(xlUp).Row).Value = "=Worksheets(""SHEET1"").Cells(i, 1).value!RC[-4]"　’エラー発生
　　　　　　　　　↓
　　　　Range("E1:S" & Worksheets(Worksheets("SHEET1").Cells(i, 1).Value).Range("B" & Rows.Count).End(xlUp).Row).Value = "=" & Worksheets("SHEET1").Cells(i, 1).value & "!RC[-4]"　’これでどうでしょう？

つまり、括弧の位置がちょっと違ったのかなぁ、と思いますよ。
で、その後ろは読んでないです（長くて読みづらいので）。
なので、その後ろにエラーがあるかどうかはわかりません。

とりあえず、私が言えること・・・
例えば「Range("E" & Rows.Count).End(xlUp).Row」など、
変数に代入しておくと、全体がスッキリしますよ。

お礼

回答ありがとうございます

補足

Range("E1:S" & Worksheets(Worksheets("SHEET1").Cells(i, 1).Value).Range("B" & Rows.Count).End(xlUp).Row).Value = "=" & Worksheets("SHEET1").Cells(i, 1).value & "!RC[-4]"　

でエラーになるシートがありました。
シート名称にスペースがあるものでエラーとなっていました。

なので、

Range("E1:S" & Worksheets(Worksheets("SHEET1").Cells(i, 1).Value).Range("B" & Rows.Count).End(xlUp).Row).Value = "='" & Worksheets("SHEET1").Cells(i, 1).Value & "'!RC[-4]"

としました。

WEBで調べると、
シート名に中括弧やスペースが入っているとエラーになるのです
というのがあり、
シート名に半角or全角の中括弧やスペースがある場合は、シート名をシングルクォートします。

ということで、上記書式にしました。

括弧の位置がちょっと違ったのかなぁ

に関して、もう少し詳しく教えていただけたらと思います。