Match Copy/Paste Macro almost there

J

james8992

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

Example:

Sheet 1: When it finds a match in "Column A" which is Sales Order to
match on Sheet 2 "Column A", it would then paste the row from Sheet
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

Jame
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top