error object variable block when match between two files and replace

[Maklil]

Hello
I no know if this code fits my project. I got this code from the internet . I want matching column B between two files . the file1 should match with file 2. if there is different the data in file 2 then should replace new data in file 1 the range is from A:E and the headers in row1
I got error object variable block in this line

 m = Application.Match(rw.Cells(1).Value, wsDest.Columns("B"), 0)

Sub Button2_Click()
    
    Dim OpenFileName As String
    Dim wb As Workbook
    Dim wsCopy As Worksheet
    Dim wsDest As Worksheet
    Dim m, rw As Range
    
    OpenFileName = Application.GetOpenFilename 'Select and Open workbook
    If OpenFileName = "False" Then Exit Sub
    
    Set wb = Workbooks.Open(OpenFileName, ReadOnly:=True)
    Set wsCopy = wb.Worksheets("Data") 'for example
    
    For Each rw In wsCopy.Range("A2:E" & wsCopy.Cells(Rows.Count, "A").End(xlUp).Row).Rows
        'matching row based on Id ?
        m = Application.Match(rw.Cells(1).Value, wsDest.Columns("B"), 0)
        'if we didn't get a match then we add a new row
        If IsError(m) Then m = wsDest.Cells(Rows.Count, "A").End(xlUp).Offset(1, 0).Row 'new row
        rw.Copy wsDest.Cells(m, "A") 'copy row
    Next rw
    
    wb.Close False 'no save
        
End Sub

any suggestion guy to do that

 

[Maklil]

thanks it works , but how can I overcome problem coping repeatedly to the bottom just I want clear before brings data (just should replace data)

 

[Alex Blakenburg]

Assuming your destination sheet has a heading in row 1 and the data starting in row 2 and the columns are "A:E" then adding this before the For loop should clear the previous content.

    With wsDest
        .Range("A2:E" & .Cells(Rows.Count, "A").End(xlUp).Offset(1, 0).Row).ClearContents
    End With

 

[Maklil]

thanks again but I face another problem , if I have new data in file where run the macro from it and not existed for the selected file will delete all of new data in file where run the macro from it . actually it shouldn't . should keep them .

 

[Alex Blakenburg]

I think I understand what you are saying. Try replacing the whole of the code from For Each rw .... through to Next rw, with the below:
Not the match line is from your original code and seems unlikely.
Since you are copying from A:E to A:E then the match should be on the same column.
So if it is column B change the reference "1" to "2", if it is on Column "A" change the "B" to "A".

    For Each rw In wsCopy.Range("A2:E" & wsCopy.Cells(Rows.Count, "A").End(xlUp).Row).Rows
        'matching row based on Id ?
        m = Application.Match(rw.Cells(1).Value, wsDest.Columns("B"), 0)
        'if we didn't get a match then we add a new row
        If IsError(m) And rw.Cells(1).Value <> "" Then
            m = wsDest.Cells(Rows.Count, "A").End(xlUp).Offset(1, 0).Row 'new row
            rw.Copy wsDest.Cells(m, "A") 'copy row
        End If
    Next rw

 

[Maklil]

I've found the problem is matching row based on Id (B) .there are duplicates ID so I change to column A
then will replace data and keep the data are existed in file run macro from it and not existed in imported data file
based on post#10 you've solved my problem .
many thanks for your assistance .

 

[Alex Blakenburg]

You're welcome. Thanks for the feedback and glad I could help.