Excel Search find and Copy

[MColeman]

Hi,

I am very new to excel and have come to an impass.

I have two spreadsheets, both with data about my customers. Each customer has a reference number (clientbank Ref).

What i need to do is to cross-check one sheet's list of clientbank ref against the other sheet without having to type each one into the search option.

Once i have assertained what clientbank ref are the same I then need those ref to be copied to another sheet in some sort of report/table where i can use them easily.

Please help.

[pike]

Hi MColeman,

Maybe you can adapt something like....

Sub ptest()
    Dim ws1 As Worksheet, ws2 As Worksheet
    Dim FoundOne As Range, LookInR As Range, LookForR As Range, c As Range
    Dim fAddress
    Application.ScreenUpdating = False
    Set ws1 = Sheets("Sheet1")
    Set ws2 = Sheets("Sheet2")
    Set ws3 = Sheets("Sheet3")
    Set LookInR = Range(ws2.Range("A1"), ws2.Range("A" & ws2.Rows.Count).End(xlUp))
    Set LookForR = Range(ws1.Range("A1"), ws1.Range("A" & ws1.Rows.Count).End(xlUp))
    For Each c In LookForR
        With LookInR
            Set FoundOne = .Find(what:=c, LookAt:=xlPart)
            If Not FoundOne Is Nothing Then
                fAddress = FoundOne.Address
                Do
                    FoundOne.Copy Destination:=ws3.Cells(Rows.Count, "A").End(xlUp).Offset(1, 0)
                    Set FoundOne = .FindNext(After:=FoundOne)
                Loop While FoundOne.Address <> fAddress
            End If
        End With
    Next c
    Set ws1 = Nothing
    Set ws2 = Nothing
    Set ws3 = Nothing
    Set LookInR = Nothing
    Set LookForR = Nothing
    Application.ScreenUpdating = True
End Sub