To me that doesn't seem like a very practical solution.
From post #1
Quote:
|
trying to setup some code for when I enter a customer name the code looks for an existing name in the list and copies 2 ranges of cells from the same row, and pastes them into the new row
|
You didn't say where those names would be entered until post #7, then indicate it to be what you were using as the .FIND range.
In post #9 you altered that .Find range.
If you're trying to do what I think you're trying to do, it can be automated straight away with the Worksheet_Change event.
Code:
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("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(, 10).Value = .Range("Y" & C.Row).Resize(, 10).Value
Application.EnableEvents = True
End If
End If
End With
End Sub