Copy & paste 2 ranges of cells

NoSparks · #11 12-02-2018, 08:28 AM

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

Similar Threads
Thread	Thread Starter	Forum	Replies	Last Post
Copy two separate ranges from another workbook	kevinbradley57	Excel Programming	11	07-25-2018 01:51 PM
Looking to copy select cells in table using dropdown list to paste to new table in another worksheet	CaptainRetired	Excel Programming	18	01-04-2018 07:22 PM
Copy/Paste EXCEL cells as pic in WORD	A_Lau	Drawing and Graphics	3	12-19-2014 06:57 AM
Can you copy & paste cells across worksheets and preserve reference to worksheet?	New Daddy	Excel	2	11-27-2013 07:19 AM
Paste Special: Copy and Paste Formatting Only?	tinfanide	Word	6	03-06-2013 12:21 AM