#1
|
|||
|
|||
Copy & paste 2 ranges of cells
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 |
#2
|
||||
|
||||
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?
|
#3
|
|||
|
|||
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
|
#4
|
|||
|
|||
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 |
#5
|
|||
|
|||
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?
|
#6
|
|||
|
|||
@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. |
#7
|
|||
|
|||
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 |
#8
|
|||
|
|||
Have you removed whatever event(s) you're not using and/or disabling and re-enabling events ?
|
#9
|
|||
|
|||
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 |
#10
|
|||
|
|||
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
|
#11
|
|||
|
|||
To me that doesn't seem like a very practical solution.
From post #1 Quote:
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
|
|||
|
|||
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
|
#13
|
|||
|
|||
Perhaps you should start a new thread, this was solved last week.
|
#14
|
|||
|
|||
Sure
Didn’t want to star new as this is almost the same thing |
#15
|
|||
|
|||
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 |