Copy & paste 2 ranges of cells

trevorc · #1 11-27-2018, 06:30 PM

Hi Again,
Currently 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 being created. below code does find the value and returns the row number for the copy selection, I originally had it do a copy of both ranges together but when pasting that it crashed excel, so I separated them in to 2 copy and paste operations. the code crashes on the second line or pastes the data starting at Column M, is it something to do with Worksheet_SelectionChange?
Any guide in the right direction would help out thanks.

Code:

Private Sub Worksheet_Change(ByVal Target As Range)
Dim Old_Value
Old_Value = Target.Value
With Sheets("Customer").Range("H4:H5000")
    Set C = .Find(Old_Value, LookIn:=xlValues)
    If Not C Is Nothing Then
    t = C.Row
        Sheets("Customer").Range("I" & t & ":L" & t).Copy
        ActiveSheet.Paste
        Sheets("Customer").Range("Y" & t & ":AH" & t).Copy
        ActiveSheet.Paste
    End If
End With
End Sub

Debaser · #2 11-28-2018, 07:08 AM

You haven't specified where to paste in the code. Where should the ranges go exactly, and do you want to paste values only, or everything?

trevorc · #3 11-28-2018, 11:36 AM

Hi, Thanks for the reply, the code needs to paste the values only into the range of cells on the same row were the new customer name is being entered. I wrongly assumed that active.cell would do this but I think that's part of my problem. I can manually find and copy the selections at the same time then paste ok, but I'd like to do this via a macro after entering the new name, either by using a macro or with the worksheet.change event

trevorc · #4 11-28-2018, 12:33 PM

Here's the code I have so far, but when I run the code I get an error - Code execution has been interrupted. If I then tell the code to continue it works fine.
Any ideas as to why

Code:

Private Sub Add_customer_data()
Dim customer_name
On Error Resume Next
customer_name = ActiveCell.Value
With Sheets("Customer").Range("H4:H5000")
    Set C = .Find(customer_name, LookIn:=xlValues)
    If Not C Is Nothing Then
    t = C.Row
    tt = C.Row
    ttt = ActiveCell.Row
    Sheets("Customer").Range("H" & ttt).Select
    Sheets("Customer").Range("I" & t & ":L" & t).Copy
    Range("I" & ttt).PasteSpecial Paste:=xlPasteValues
    Application.CutCopyMode = False
    Sheets("Customer").Range("Y" & t & ":AH" & t).Copy
    Sheets("Customer").Range("Y" & ttt).Select
    Range("Y" & ttt).PasteSpecial Paste:=xlPasteValues
    Application.CutCopyMode = False
    Sheets("Customer").Range("H" & ttt).Select
    End If
End With
End Sub

trevorc · #5 11-28-2018, 12:56 PM

A quick update, I do have data validation in some of the cells being pasted into, could this cause it? if so would adding a delay help?

NoSparks · #6 11-28-2018, 06:15 PM

@trevorc
you've still neglected to mention:
- the name of the sheet the Worksheet_Change event would be used on
- the column or range of cells the Worksheet_Change event would be restricted to
- the location (ie: starting column) where Sheets("Customer").Range("I" & t & ":L" & t) would be copied to
- the location (ie: starting column) where Sheets("Customer").Range("Y" & t & ":AH" & t) would be copied to

Posting a sample workbook would be to your advantage.

trevorc · #7 11-28-2018, 06:26 PM

Thanks for replying, doesn't the selections in red cover that.
Also I have added an option, to the right click event for the table, to run the macro.

It does all do what I need but keeps coming back with - Code execution has been interrupted. after pressing continue it completes correctly, in VBA it errors after the first paste line, hitting F5 to continue works ok too. I have removed all worksheet events to see if they were causing it, but still fails.

- the name of the sheet the Worksheet_Change event would be used on
Sheets("Customer")
- the column or range of cells the Worksheet_Change event would be restricted to
With Sheets("Customer").Range("H4:H5000")
- the location (ie: starting column) where Sheets("Customer").Range("I" & t & ":L" & t) would be copied to
Sheets("Customer").Range("I" & t & ":L" & t).Copy
Range("I" & ttt).PasteSpecial Paste:=xlPasteValues
- the location (ie: starting column) where Sheets("Customer").Range("Y" & t & ":AH" & t) would be copied to
Sheets("Customer").Range("Y" & ttt).Select
Range("Y" & ttt).PasteSpecial Paste:=xlPasteValues

NoSparks · #8 11-28-2018, 06:59 PM

Have you removed whatever event(s) you're not using and/or disabling and re-enabling events ?

trevorc · #9 11-29-2018, 01:00 PM

I have my code working ok now, the issue I had was that any code that changed/added data to cells was bringing up the error - Code execution was interrupted. I found this online Application.EnableCancelKey = xlDisabled to stop that happening, but have to place it in all macros that cause the error. is there a way to do this only once in the project only?

Code:

Private Sub Add_customer_data()
Application.EnableCancelKey = xlDisabled
Dim customer_name
customer_name = ActiveCell.Value
With Sheets("Customer").Range("H4:H" & ActiveCell.Row - 1)
    Set C = .Find(customer_name, LookIn:=xlValues)
    If Not C Is Nothing Then
    t = C.Row
    ttt = ActiveCell.Row
    Application.EnableEvents = False
    Sheets("Customer").Range("H" & ttt).Select
    Sheets("Customer").Range("I" & t & ":L" & t).Copy
    Range("I" & ttt).PasteSpecial Paste:=xlPasteValues
    Application.CutCopyMode = False
    Sheets("Customer").Range("Y" & t & ":AH" & t).Copy
    Sheets("Customer").Range("Y" & ttt).Select
    Range("Y" & ttt).PasteSpecial Paste:=xlPasteValues
    Application.CutCopyMode = False
    Sheets("Customer").Range("H" & ttt).Select
    End If
End With
End Sub

trevorc · #10 11-29-2018, 06:44 PM

Just a follow up on the error, still don't know what it is that causes it but a better solution is - run code, when the msg comes up go to debug, hit Ctrl+Break 'Twice', finish running the code, save and close the file then reopen the file and the problem is gone. no need to add - Application.EnableCancelKey = xlDisabled

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

#12 12-02-2018, 01:04 PM

No Sparks just to say this is great but how will code look like if i want to cut not 'copy from C.row to Target.row if C.Row is in other sheet (for example sheet7) and what needs to be added if nothing found in sheet7 ( my case) to move to next cell to the right

trevorc · #13 12-02-2018, 01:19 PM

Perhaps you should start a new thread, this was solved last week.

#14 12-02-2018, 01:37 PM

Sure

Didn’t want to star new as this is almost the same thing

NoSparks · #15 12-02-2018, 03:08 PM

Yes AC PORTA VIA please start a new thread and please include a sample workbook so there's no doubt as to what and where things are that you're working with.

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