Copy column data from one sheet to another and delete duplicate entries

  • How do I copy data from sheet 1 column A to the first empty cell in sheet 2 column A , check Sheet 2 Column A for any duplicate entries and delete them. I have uploaded a copy the test workbook. The copy & paste function for Sheet 2 should be when the sheet is activated. Thanks

  • Re: Copy column data from one sheet to another and delete duplicate entries


    Hi Oracle


    Put this in the Sheet2 Worksheet Object module.


    Code
    1. Private Sub Worksheet_Activate()
    2. Sheet1.Range("A2", Sheet1.Range("A" & Rows.Count).End(xlUp)).Copy Sheet2.Range("A" & Rows.Count).End(xlUp)(2)
    3. Sheet2.Range("A2", Sheet2.Range("A" & Rows.Count).End(xlUp)).RemoveDuplicates 1
    4. End Sub


    Will attach a worksheet to prove workings.


    Take care


    Smallman

  • Re: Copy column data from one sheet to another and delete duplicate entries


    Thanks so much. I was trying but just couldn't get it. I tested your code and it does copy and delete the duplicate records but it does not start with the first empty cell in sheet 2 column A thus it may result in blank cells. Is there a way to address that requirement.

  • Re: Copy column data from one sheet to another and delete duplicate entries


    Oracle


    If you are trying to say that it does not paste to the first blank cell in Sheet 2 then you may want to check that. This line;


    Code
    1. Sheet2.Range("A" & Rows.Count).End(xlUp)(2)


    absolutely guarantees that the data hits the first blank cell of sheet2. To prove it step through the code with F8. Been using it for years and it goes like thunder. :)


    Take care


    Smallman

  • Re: Copy column data from one sheet to another and delete duplicate entries


    In sheet1 code

    Code
    1. Private Sub Worksheet_Activate()
    2. Call Treat
    3. End Sub


    in a module

    Files

    Triumph without peril brings no glory: Just try

  • Re: Copy column data from one sheet to another and delete duplicate entries


    Ok. I tested the code again. While it does find the next empty cell if there was a blank cell earlier on it does not fill that first. I have posted the sample workbook to illustrate

  • Re: Copy column data from one sheet to another and delete duplicate entries


    In that case make a minor adjustment to the code;


    Code
    1. Private Sub Worksheet_Activate()
    2. Sheet1.Range("A2", Sheet1.Range("A" & Rows.Count).End(xlUp)).Copy Sheet2.Range("A2").End(xlDown)(2)
    3. Sheet2.Range("A2", Sheet2.Range("A" & Rows.Count).End(xlUp)).RemoveDuplicates 1
    4. End Sub


    Take care


    Smallman

  • Re: Copy column data from one sheet to another and delete duplicate entries


    Thanks so much. I works perfectly. I spent hours trying to figure out how to do this. I cant believe it only took 2 lines of code. Thanks again

  • Re: Copy column data from one sheet to another and delete duplicate entries


    For some reason the following line of code is throwing out a runtime error 1004 is there a work around for this


    Code
    1. Sheet1.Range("A2", Sheet1.Range("A" & Rows.Count).End(xlUp)).Copy Sheet2.Range("A2").End(xlDown)(2)
  • Re: Copy column data from one sheet to another and delete duplicate entries


    I don't see what you mean Oracle. The attached shows the code working perfectly with or without a space in sheet 2. You are going to have to be more specific as your concerns appear not to stack up. Post a file.


    Take care


    Smallman

  • Re: Copy column data from one sheet to another and delete duplicate entries


    I would suggest

    Code
    1. Sheet1.Range("A2:A" & Sheet1.Range("A" & Rows.Count).End(xlUp).Row).Copy _
    2. Destination:=Sheet2.Range("A" & Rows.Count).End(xlUp).Offset(1, 0)

    Triumph without peril brings no glory: Just try

  • Re: Copy column data from one sheet to another and delete duplicate entries


    Thanks Smallman. I copied and pasted back the code and its working again. I dont know why it through that error it was working fine before then all i was getting was debug runtime error. Thanks again for your quick reply

  • Re: Copy column data from one sheet to another and delete duplicate entries


    how can i modify the code below to remove not only the duplicate record but the entire row its in as well


    Code
    1. Sheet1.Range("A2:A" & Sheet1.Range("A" & Rows.Count).End(xlUp).Row).Copy _Destination:=Sheet2.Range("A2:A" & Rows.Count).End(xlUp).Offset(1, 0)
    2. Sheet2.Range("A2", Sheet2.Range("A" & Rows.Count).End(xlUp)).RemoveDuplicates 1
  • Re: Copy column data from one sheet to another and delete duplicate entries


    Ok did some more research and combined it with the assistance I got on this forum. Sample Workbook is attached.
    Overall, it seems stable and appears to work but needs some error handling for when the primary column to be copied is empty.
    Also it takes a while to run so i'm pretty sure its not as efficient as it needs to be.
    Any Help with Error Handling and Making the Code more efficient is greatly appreciated, as this is as far as I can take it with my noob skills


    Features:


    • Copy 1 Column from 1 sheet to another
    • Eliminates duplicate records
    • Ensures that changes made to primary column are reflected on the other sheet


  • Re: Copy column data from one sheet to another and delete duplicate entries


    Hi oracle259,


    Might I suggest moving this to a new thread? You will probably get more replies as your most recent post no longer reflects what the thread title is asking.


    It's good to see people wanting to learn error handling though, far too many people using "On Error Resume Next" these days...


    Take Care :smile:

  • Re: Copy column data from one sheet to another and delete duplicate entries


    Oracle


    This should help.


    Put this in the WS change event. Yourr procedure seems overly complex to me and you should be able to adapt to suit.


    Code
    1. Sub test()
    2. Sheet1.Range("A2", Sheet1.Range("A" & Rows.Count).End(xlUp)).Copy Range("A2:A" & Rows.Count).End(xlUp)(2)
    3. Range("A2", Range("A" & Rows.Count).End(xlUp)).Offset(, 1).Formula = "=COUNTIF(A$1:A1,A2)=0"
    4. Range("B2", Range("B" & Rows.Count).End(xlUp)).AutoFilter 1, "FALSE"
    5. Range("B2", Range("B" & Rows.Count).End(xlUp)).EntireRow.Delete
    6. Columns(2).Clear
    7. ActiveSheet.AutoFilterMode = False
    8. End Sub


    Take care


    Smallman

  • Re: Copy column data from one sheet to another and delete duplicate entries


    It does execute the copy, checks and deletes duplicates. But it also deletes the data in the first row A2 (A1 contains Headers)