Copying one row from one table to the end of another when the value of a cell changes

  • Hi, I've been searching online and doing a lot of trial and error but I haven't been able to make it word. If someone could kindly help me, I'll apreciate very much.


    On Worksheet "Original" there's Table1 with columns [Name] [Number] [Info] [Status]. There are no rows with the same [Name], as Table1 works as a Contact List.
    On Worksheet "History" there's Table2 with columns [Name] [Number] [Date]. There can be duplicates for [Name] as Table2 works as a Log.


    What I'm trying to do is that everytime I chance the value of [Status] to "Called", the values of [Name] and [Number] from that specific row in Table1 will be copied to a new row in Table2, and in that new row, under [Date], will be added today's date.


    If you would like I can add some of the code Ive been trying without success.


    Thanks in advance!

  • Put this in your worksheet_change event for the sheet named "Original"


    Copy my code
    Open the VBE (Alt F11)
    double click on the Sheet Original in the left window.
    Paste the code into the window that opens.
    Close the VBE and Save the file.

  • Thank you, AlanSidman.
    Your code is much more elegant then the others I've been trying.
    Yet, nothing happens when changing the [Status] to Called.
    I Tried both copying the Code to my own Worksheet and directly in the sample.xlsm you provided.
    Do you know what could it be?
    If it makes a difference, I'm using Excel in Portuguese, and although have to translate the formulas in order for them to work, I haven't seen a similiar issue with the codes.

  • Would it be too much trouble to modify the code so it doesn't copy the format, only the value?


    I've added

    Code
    s2.Range("A:B").ClearFormats

    to the code and it did the trick, since only table1 has formats.

  • Try this:


Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!