Macro in Excel

1. Enable Developer Toolbar (if not yet): Home -> Right click ribbon toolbar -> Customize Ribbon -> Check Developer checkbox.
2. Developer toolbar -> Macro -> Test this code:

 

Sub test()

    Dim r As Range, c As Range

    Dim x As String, j As Integer, k As Integer

    Dim cfind As Range, r1 As Range

    

    With Workbooks("excel A.xls").Worksheets("sheet1")

        Set r = Range(.Range("A2"), .Range("A2").End(xlDown))

        For Each c In r

            x = c.Value

            With Workbooks("excel B.xls")

                j = .Worksheets.Count

                For k = 1 To j

                    With .Worksheets(k)

                        Set cfind = .Cells.Find(what:=x, lookat:=xlWhole)

                        If Not cfind Is Nothing Then

                            Set r1 = Range(cfind.Offset(0, 1), cfind.End(xlToRight))

                            r1.Copy

                            GoTo pasting

                        End If

                    End With 'worksheets(k)

                    Next k

                    Exit Sub

            End With 'second book

        

pasting:

        c.Offset(0, 3).PasteSpecial

        Next c

    End With 'first book

End Sub