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
 

Some videos you may like

Excel Facts

Control Word Wrap
Press Alt+Enter to move to a new row in a cell. Lets you control where the words wrap.

Peter_SSs

MrExcel MVP, Moderator
Joined
May 28, 2005
Messages
47,926
Office Version
  1. 365
Platform
  1. Windows
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?
 

mattrx731

Board Regular
Joined
Feb 10, 2008
Messages
181
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.
 

pbornemeier

Well-known Member
Joined
May 24, 2005
Messages
3,884

ADVERTISEMENT

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
 

mattrx731

Board Regular
Joined
Feb 10, 2008
Messages
181
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?
 

pbornemeier

Well-known Member
Joined
May 24, 2005
Messages
3,884
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
 

mattrx731

Board Regular
Joined
Feb 10, 2008
Messages
181
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
 

Watch MrExcel Video

Forum statistics

Threads
1,122,842
Messages
5,598,396
Members
414,235
Latest member
sucosama

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
Top