To copy data from other workbook until the last data and paste to active workbook

To copy data from other workbook until the last data and paste to active workbook


Sub CopyData()
    Dim Wb1 As Workbook, wb2 As Workbook, wB As Workbook
    Dim rngToCopy As Range

    For Each wB In Application.Workbooks
        If Left(wB.Name, 21) = "Book1" Then
            Set Wb1 = wB
            Exit For
        End If
    Next

    If Not Wb1 Is Nothing Then '<~~ check if you actually found the needed workbook
        Set wb2 = ThisWorkbook

        With Wb1.Sheets("Sheet1")
            Set rngToCopy = .Range("A2:AM2", .Cells(.Rows.Count, "A").End(xlUp))
        End With
        wb2.Sheets("Sheet2").Range("B5:AN5").Resize(rngToCopy.Rows.Count).Value = rngToCopy.Value
    End If
End Sub