VBA Copy select cells from row in source worksheet to target worksheet if column A values match

Question

I need a VBA script that will look at the account number in column A of the source worksheet and find it's exact match in column A the target worksheet. When the match is found, it needs to copy cells "J" through "M" from the source worksheet into cells "O" through "S" of the target worksheet.

There are about 80 rows in the source worksheet and over 500 rows in the target worksheet. The account numbers in both worksheets will have exact matches, but the numbers aren't sequential from one row to the next.

Any help is greatly appreciated.

Answer 1

Something like this might work for you:

For i = 1 to 90
    For j = 1 to 600
        If SourceWorksheet.Range("A" & i).Value _ 
            = TargetWorksheet.Range("A" & j).Value Then
            TargetWorksheet.Range("O" & j, "S" & j) _  
                = SourceWorksheet.Range("J" & i, "M" & i)
        End If
    Next j
Next i

Answer 2

Sheet target
Sheet source
String accountNum

set target = Workbook.sheet("sheetName")
set source = ActiveSheet

accountNum = Selection.cell(1,1)

Boolean found
Integer i
i = 1
found = false
while(target.cell(i,1) <> "" AND NOT found)
    if (target.cell(i,1) = accountNum) then found = true
wend

if not found then return

for Integer j = 0 to 3
    target.cell(i, j + 15) = source.cell(Selection.Row, j + 10)
next

Please note, I haven't done much VBA in years so syntax may be off.