Thread: VBA to Copy single row, find match in sheet2 & paste over

Hi

I have 2 sheets each with the same column header data in both. (Columns A - BP)
Sheet1 has over 1000 rows of data with a unique number in column A assigned to each row.

Sheet2 has only one row (row 2) [row 1 has column headers].
I need to copy the row in Sheet2, find the matching row in Sheet1 (Unique number in Column A). Once found, the copied data needs to be pasted over the existing data in that row in Sheet 1 using PasteSpecial values.

Your assistance is greatly appreciated.
Thanks

Hi, Give this a try:

Option Explicit
Sub copyfrom()
Dim lrow As Long
Dim rfound As Range
Dim i As Integer
Application.ScreenUpdating = False
    lrow = Sheets(2).Cells(Rows.Count, 1).End(xlUp).Row
        With Sheets(2)
            For i = 2 To lrow
                Set rfound = Sheets(1).Cells.Find(What:=.Cells(i, 1).Value, After:=.Cells(1, 1), LookIn:=xlFormulas, LookAt:= _
                xlWhole, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:=False _
                , SearchFormat:=False)
 
                    If rfound Is Nothing Then
 
                    Else
                    Sheets(2).Cells(i, 1).EntireRow.Copy Sheets(1).Range(rfound.Address)
 
                    End If
 
 
            Next i
End With
Application.ScreenUpdating = True
End Sub