Copy and paste after worksheet change...?

mattrx731

Board Regular
Joined
Feb 10, 2008
Messages
181
I'm using this formula in a table column (AJ) on sheets "rentals"
Code:
=CONCATENATE($A2,", ",$B2)

I'd like to automatically copy that cell to the last empty row in column T on sheets "Lists"
I'm assuming i'd use a worksheet change event, but I'm not sure.
Thanks
 

Excel Facts

Create a chart in one keystroke
Select the data and press Alt+F1 to insert a default chart. You can change the default chart to any chart type
Also, are you sure you want to copy it to the last empty row? That would most likely be row 65,536 (or row 1,048,576 for Excel 2007). Maybe you mean the first empty row?
 
Upvote 0
A first and last name are entered into columns A and B, which populates the column AJ all on sheet "Rentals"... after AJ has the information in it, I would like it to automatically copy it to FIRST empy row on sheet "Lists" in column T.
I don't want it to add a row, just add the data from the newly updated entry in column AJ, to the first empty cell in the destination.
 
Upvote 0
I believe this will do what you asked for. It should be pasted into the 'Rentals' worksheet code page. After column B on 'Rentals' worksheet is changed and the cursor has left the cell, column A & B on that row will be concatenated and placed in the first blank cell in column T of the 'Lists' worksheet. Note that this will occur when a blank cell is filled in and when a filled in cell is edited.
Code:
Private Sub Worksheet_Change(ByVal Target As Range)

    Dim rngInput As Range
    Dim intX As Integer
    
    Set rngInput = Intersect(Target, Columns("B"))
    If Not rngInput Is Nothing Then
        
        intX = Worksheets("Lists").Range("T" & Rows.Count).End(xlUp).Row + 1
        Worksheets("Lists").Range("T" & intX).Value = Cells(Target.Row, 1) & ", " & Cells(Target.Row, 2)
        
    End If
    Set rngInput = Nothing
End Sub
 
Upvote 0
That does exactly what I asked for thanks..
Note that this will occur when a blank cell is filled in and when a filled in cell is edited.
is it possible to replace the original, rather than pasting to the first empty cell?
 
Upvote 0
Unless the data from row X in the 'Rentals' worksheet is copied the same row (instead of the next in the 'Lists' worksheet, there would be no way to know what row the original data was copied from - and no way to update the correct entry on the 'Lists' worksheet. Another alternative: Is one of the other columns in the 'Rentals' worksheet a unique key -- such as invoice number? This could be copied to another column in the 'Lists' worksheet and provide a link between the original and changed names.

If copying to the same row is OK, you could use this formula copied down in column T of the 'Lists' worksheet:
Code:
=Rentals!A2&", "&Rentals!B2
 
Upvote 0
I think I'll leave it alone for right now... I won't be editing the data very often...
Thanks a lot for the help
 
Upvote 0

Forum statistics

Threads
1,213,511
Messages
6,114,054
Members
448,543
Latest member
MartinLarkin

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top