Transferring Data Between Worksheets

[mharper90]

I'm trying to compare columns of first and last names (A:B) on ws1 with columns (A:B) on ws2. If the name exists on ws2, then I need to copy the value of ws1 columns (I:L) to ws2 columns (D:G) for the matching row.

 

[Mentor82]

Hi,
Try this.

Sub copydata()
Dim ws1 as worksheet
Dim ws2 as worksheet
Dim lr1&, lr2&
Dim c as range, c2 as range

Set ws1=sheets("Sheet1")
Set ws2=sheets("Sheets2")
lr1=ws1.range(rows.count,"a").end(xlup).row
lr2=ws2.range(rows.count,"a").end(xlup).row
For each c in ws1.range(range("A1"),range("A1").offset(lr1-1,0))
    For each c2 in ws2.range(range("A1"),range("A1").offset(lr2-1,0))
         If c.value & c.offset(0,1).value = c2.value & c2.offset(0,1).value then
            ws1.range(c.offset(0,8),c.offset(0,11)).copy ws2.range(c2.offset(0,3),c2.offset(0,6))
         Exit for
         end if
    Next c2
Next c

Set ws1=nothing
Set ws2=nothing
End sub