Hi All,
I'm trying to search 2 sheets for a customer name and if found copy/paste some of the cells into the same row. This works great when I'm searching on the same sheet and returns the data correctly, how can I add to this code so if it can't find a match in the current sheet it looks in another sheet for the data there, and if found copy the same range of cells into the current sheet.


Any help is appreciated, even just a point in the right direction would be great.




P.S. I tried to wrap my code in code tags but it wouldn't let me do it for some reason




Private Sub Worksheet_Change(ByVal Target As Range)
Dim C As Range
'limit to single cell
If Target.Count > 1 Then Exit Sub
With Sheets("Clipsal Customer")
'monitor specific range
If Intersect(Target, .Range("H4:H5000")) Is Nothing Then Exit Sub
'dealing with the entry
If Target.Value <> "" Then
'.Find has some persistence
Set C = .Range("H4:H" & Target.Row - 1).Find(What:=Target.Value, LookIn:=xlValues, LookAt:=xlWhole, MatchCase:=False)
If Not C Is Nothing Then
'copy from C.row to Target.row
Application.EnableEvents = False
.Range("I" & Target.Row).Resize(, 4).Value = .Range("I" & C.Row).Resize(, 4).Value
.Range("Y" & Target.Row).Resize(, 9).Value = .Range("Y" & C.Row).Resize(, 9).Value
Application.EnableEvents = True
End If
End If
End With
End Sub