VBA 別ブックの複数のシートをマクロを実行するブックのシート１にまとめたい。

Question

別ブックの複数のシートをマクロを実行するブックのシート１にまとめたい。

以下のような流れのマクロを作成しようとしています。
1.マクロ実行
2.別ブック（コピー元のブック）を開く。
3.別ブック（コピー元のブック）のシート1のA2から最終列と最終行までの内容をコピーする。
4.本ブック（マクロを実行するブック）のシート1の最終行の1つ下のセルに値貼り付けする。
5.別ブック（コピー元のブック）のシート2のA2から最終列と最終行までの内容をコピーする。
6.本ブック（マクロを実行するブック）のシート1の最終行の1つ下のセルに値貼り付けする。
あとは別ブック（コピー元のブック）の最終シートまで上記の3-6を繰り返す。

しかし4で以下のエラーが発生します。
実行時エラー　424
オブジェクトが必要です。

どうすればエラーを回避して希望する処理を進められますでしょうか？

コードは以下の通りです。

Sub まとめるマクロ()

    Dim i As Integer
    Dim 事業所別コード社員一覧 As String
    
    事業所別コード社員一覧 = Dir("*事業所別コード社員一覧.xls*")
    
    Workbooks.Open 事業所別コード社員一覧
    
    For i = 1 To Workbooks(事業所別コード社員一覧).Worksheets.Count
        Worksheets(1).Range("A2", Range("A2").End(xlToRight).End(xlDown)).Copy
        ThisWorkbook.Worksheets(1).Cells(Rows.Count, 1).End(xlUp).Row.Offset(1, 0).PasteSpecial (xlValues)'ここでエラーが出ます
    Next
    
End Sub

Answer 1

.Rowが余分ですね。Rowは何行目かという数値ですのでそのエラーになります。
あと、Worksheets(1) は Worksheets(i)じゃないですか。

        Worksheets(i).Range("A2", Range("A2").End(xlToRight).End(xlDown)).Copy
        ThisWorkbook.Worksheets(1).Cells(Rows.Count, 1).End(xlUp).Row.Offset(1, 0).PasteSpecial xlValues 

コメントを受けて追記

さらに、Range や Cells の中の Range("A2") や Rows.Count もブック.シートを指定しないと、エラーになりますね。

    For i = 1 To Workbooks(事業所別コード社員一覧).Worksheets.Count
        With Workbooks(事業所別コード社員一覧).Sheets(i)
            .Range("A2", .Range("A2").End(xlToRight).End(xlDown)).Copy
        End With
        With ThisWorkbook.Worksheets(1)
            .Cells(.Rows.Count, 1).End(xlUp).Offset(1, 0).PasteSpecial xlValues
        End With
    Next

ちょっとコードが読みづらくなるので、ここは変数をうまく使うとぐっと読みやすくなります。

Sub まとめるマクロ()

    Dim 事業所別コード社員一覧 As String
    事業所別コード社員一覧 = Dir("C:\TEST\test1.xlsm")

    Dim fromWB As Workbook
    Set fromWB = Workbooks.Open(事業所別コード社員一覧)
    
    Dim toWS As Worksheet
    Set toWS = ThisWorkbook.Worksheets(1)

    Dim i As Integer
    For i = 1 To fromWB.Worksheets.Count
        fromWB.Sheets(i).Range("A2", fromWB.Sheets(i).Range("A2").End(xlToRight).End(xlDown)).Copy
        toWS.Cells(toWS.Rows.Count, 1).End(xlUp).Offset(1, 0).PasteSpecial xlValues
    Next

End Sub

Comments

[msys1987]

回答ありがとうございます！
おかげさまでエラーが回避できました。

しかし別のエラーが発生しました。
実行時エラー1004　アプリケーション定義またはオブジェクト定義のエラーです。

以下のようにコードを変更しました。

For i = 1 To Workbooks(事業所別コード社員一覧).Worksheets.Count
　Workbooks(事業所別コード社員一　　　　　　覧).Sheets(i).Range("A2",Range("A2").End(xlToRight).End(xlDown)).Copy　’ここでエラーがでます。
ThisWorkbook.Worksheets(1).Cells(Rows.Count, 1).End(xlUp).Offset(1, 0).PasteSpecial (xlValues)
Next

別ブック（コピー元のブック）のWorksheets(2)が見つからないのかな？と思い、変数を使わず
Workbooks(事業所別コード社員一　　　　　　覧).Sheets(2).Range("A2",Range("A2").End(xlToRight).End(xlDown)).Copy
に変更しましたが、同現象です。

別ブック（コピー元のブック）でSheets(2)をセレクトするマクロを組みましたが、正しく動作しています。
矢継ぎ早で申し訳ありません。ご教授いただけませんでしょうか。

[hatena19]

回答に追記しましたのでご参照ください。

[msys1987]

ありがとうございました。おかげさまで希望する動作のマクロができました。
RangeやRows.Countにもブックの指定が必要なのですね。勉強になりました。
ありがとうございました。