[james8992]

I found the following code for a macro that almost does everything I need it to. Right now what the code does is it find a match and pastes the value in the cell right next to it. But rather then just pasting one value I'd like it to paste the entire row next the the match on Sheet1.

Example:

Sheet 1: When it finds a match in "Column A" which is Sales Order to a match on Sheet 2 "Column A", it would then paste the row from Sheet 2 next to the match on Sheet 1

Option Explicit
Sub CopyIDData()
Dim rng As Range
Dim rng1 As Range
Dim cell As Range
Dim res As Variant

With Worksheets("sheet1")
Set rng = .Range("A1", .Cells(.Rows.Count, "A").End(xlUp))
End With

With Worksheets("Sheet2")
Set rng1 = .Range("A1", .Cells(.Rows.Count, "A").End(xlUp))
End With

For Each cell In rng.Cells
If Trim(cell.Value) = "" Then
'skip it
Else
res = Application.Match(cell, rng1, 0)
If Not IsError(res) Then
rng1(res, 2).Resize(1, 1).Copy Destination:=cell.Offset(0, 1)
End If
End If
Next cell
End Sub

Thanks

James