vba to copy specific cells when values from two columns match

Question

I'm new to vba and need to find a way to copy certain cells between worksheets based on matching values. In sheet1, column A is a list of abbreviated names. I need to search sheet 2, column D for any matches. If a match is found, copy the cells in A, B, and C of sheet 2 and paste them into the corresponding location in sheet 1.

1) record Macro, 2) vlookup function. Google these two and you might have it done in no time — Alex
I don't think I can just record a macro as there are hundreds of separate matching values, not just a single value that I am looking for. I'm not overly familiar with the vlookup function. I've been trying to learn vba and often find code that I can modify to accomplish what I need but this is still eluding me. — Allan B

Golemic Golemic · Accepted Answer · 2014-11-14T16:48:45

I also believe that the easiest way is using vlookup. Might faster too... You can use something like this if you like. Haven't check it though.

Sub CopyBasedonSheet1()

Dim i As Long
Dim j As Long
Sheet1LastRow = Worksheets("Sheet1").Range("A" & Rows.Count).End(xlUp).Row
Sheet2LastRow = Worksheets("Sheet2").Range("D" & Rows.Count).End(xlUp).Row

    For j = 1 To Sheet1LastRow
        For i = 1 To Sheet2LastRow
            If  Worksheets("Sheet1").Cells(j, 1).Value  = Worksheets("Sheet2").Cells(i, 4).Value Then
                Worksheets("Sheet1").Cells(j, 2).Value  = Worksheets("Sheet2").Cells(i, 1).Value
                Worksheets("Sheet1").Cells(j, 3).Value  = Worksheets("Sheet2").Cells(i, 2).Value
                Worksheets("Sheet1").Cells(j, 4).Value  = Worksheets("Sheet2").Cells(i, 3).Value
            Else
            End If
    Next i
Next j
End Sub

vba to copy specific cells when values from two columns match

1 Answers