インデックス範囲が有効ではありませんについて

Question

現在会社の個別の勤怠管理票を作り、それを集計するvbaを作りたく勉強しています。
そこでまず一人だけ（例では田中さん）についての勤怠管理票を開いて、それを集計用の別のブックのシートの転記を試みました。しかしインデックスが有効ではありませんと出ました。以下は私が書いたコードです。

Sub 田中勤務時間()

Application.ScreenUpdating = False

Dim path As String
Dim j As Long

path = "C:\Users\tanak\OneDrive\デスクトップ\勤怠時間\"

For j = 1 To 5
Workbooks.Open path & "田中.xlsx" ※ここが田中さんの勤怠用のエクセルブックを表しています。
Worksheets("Sheet2").Cells(4, 1 + j).Value = Cells(4, 1 + j).Value　←ここでインデックスが有効ではありませんと出ます。
ActiveWorkbook.Close savechanges:=False
Next j

End Sub

おそらく今開いているエクセル(集計用のエクセル）と田中さんのエクセルファイルを区別できていないため、正常に作動しないと予想していますが、正しいコードが調べても分からなく、アドバイス頂けないでしょうか。宜しくお願い致します。

Comments

[meg_]

＞ Worksheets("Sheet2").Cells(4, 1 + j).Value = Cells(4, 1 + j).Value
上記コードのどこでエラーが発生しているのかを確かめましょう。まず、「Worksheets("Sheet2")」は存在していますか？

[somechan1]

シート名は開いているブックに存在していますが、転記先のブック名だと正常に実施されなかったのですが、thisworkbookだと正常に作動しませんでした。

・作動したコード
ThisWorkbook.Worksheets("Sheet2").Cells(4, 1 + j).Value = Cells(4, 1 + j).Value

・作動しなかったコード
Workbooks("集計").Worksheets("Sheet2").Cells(4, 1 + j).Value = Cells(4, 1 + j).Value

後学のため、理由など教えて頂けると幸いです。

Answer 1

おそらく今開いているエクセル(集計用のエクセル）と田中さんのエクセルファイルを区別できていないため

区別できてないではなく、
書いてないので勝手に解釈されているのです。
複数ブック、複数シートを扱う場合は、
ブックやシートを明示したコードを書くようにしなければ、
エラーや意図しない結果が出ても、仕方がないです。

インデックス範囲が有効ではありません

このエラーは、指定したインデックス番号や名前が存在しないときに出ます。

Worksheets("Sheet2").Cells(4, 1 + j).Value

↑は、Activewindow.Worksheets("Sheet2").Cells(4, 1 + j).Value
と解釈されますし、

Cells(4, 1 + j).Value

↑は、Activewindow.activesheet.Cells(4, 1 + j).Value
と解釈されますので、気を付けましょう。

また、
Workbooks.Openメソッドは、「返り値」がありますので、
それを変数に受けておけば、
なんども、workbooks(勤怠用エクセルのファイル名）と指定しなくても、
変数を利用して指定できます。

Sub 田中勤務時間()

    'Application.ScreenUpdating = False

    Dim path As String
    Dim wbkFrom As Workbook
    Dim wbkTo As Workbook

    path = "C:\Users\tanak\OneDrive\デスクトップ\勤怠時間\"
    Set wbkFrom = Workbooks.Open(path & "田中.xlsx") '※ここが田中さんの勤怠用のエクセルブックを表しています。
    Set wbkTo = ThisWorkbook
    'set wbkto = workbooks(1)           '開いた順番で番号が振られるので番号で指定しても良い
    'set wbkto = workbooks("ファイル名") 'ブック名でもよいし、フルパスでもよい
    
    wbkTo.Sheets("Sheet2").Range("D1:D5").Value = wbkFrom.Worksheets(1).Range("D1:D5").Value
    
    wbkFrom.Close False
End Sub

Comments

[somechan1]

workbook.openを変数にできるとは、初耳でした。とても勉強になりました。ありがとうございます。

Answer 2

今開いているエクセルは集計用のエクセルではなく、田中さんの勤怠用のエクセルになるため区別できていないですね
今開いているエクセルは直近にWorkbooks.Openで開いたエクセルになります
おそらく集計用のエクセルのsheet２のセルに値を入れたいのでしょうが、今開いているエクセル（田中さんの勤怠用のエクセル）にsheet２という名前のシートがないためエラーになります
Worksheets("Sheet2").Cells(4, 1 + j).Value = Cells(4, 1 + j).Valueを
workbooks(勤怠用エクセルのファイル名）.Worksheets("Sheet2").Cells(4, 1 + j).Value = Cells(4, 1 + j).Valueに変えると良いでしょう
もしくはThisWorkBookを使うと良いです

Comments

[somechan1]

takkさん、コメントありがとうございました。
頂いたアドバイスで実施できたのですが、下記のパターンに分かれました。
thisworkbook →　出来ました
workbooks(勤怠用エクセルのファイル名） →　出来ませんでした

後学のため、理由を教えて頂けると幸いです。宜しくお願い致します。

[takk_014]

thisworkbookでマクロを動かしているエクセルファイルを指定できます
すみません、誤字ですがworkbooksの最初のwは大文字のWでした
そして最後のsは必要ないため
Workbook(勤怠用エクセルのファイル名）
が正しいです
久しぶりにVBAに触れるので忘れていました

[somechan1]

私もsを付けていたかもしれません。初歩的な質問で申し訳御座いませんでした。