Filter, Copy and Paste the Unique values in the next available row of another workbook

    Hello,



    I would like to ask you for help with my macro that I want to write. I tried to do it but I couldn't



    I am trying to copy the values with conditions to the another worksheet but I got lost while writing a macros as I am pretty new to VBA programming.



    Here is the some more detail of the macros that I would like to pull out,



    I would like to copy the unique values(Column A contains unique values) from this file "Employee(copy).xlsm" to the last available row of "Employee(final).xlsx",


    I would like to apply two conditions, 1) Column C > 01/05/2019(dd/mm/yyyy) 2) Column D = Nicolas and Charles



    So whenever the data is added in the source file with the unique values(Column A) then code needs to copy and paste the data in the next available row of the target file but I don't wanna clear the values which is already in the target file every time and add the unique values but just add the new unique values in the next available row insteadEmployee(copy).xlsm



    Your help will be appreciated [Blocked Image: https://www.excelforum.com/images/smilies/smile.gif]



    Thank you in Advance

  • Carim

    Changed the title of the thread from “Fiter, Copy and Paste the unique values in the next available row of the another workbook” to “Filter, Copy and Paste the Unique values in the next available row of another workbook”.

    Hi and Welcome to the Forum :)


    From your comments, it would appear the problem does need to be slightly re-defined ...


    1. Are you already operating with two existing workbooks ?

    2. Whenever you need an update, are your two workbooks opened or closed ?

    3. Do you actually need a comparison between the data held in both workbooks ?

    4. With Unique Values in Column A, there is no need to worry about this column ... Yes or No ?

    5. Are you looking for "a conditional copy" as soon as you update workbook 1 ?


    Hope this will help you clarify your need

    If you feel like saying "Thank You" for the help received, do not hesitate to click the "Smiley" icon, below, in the bottom right corner :)

    Hello Carim,


    Thank you :)


    1. Are you already operating with two existing workbooks ? Yes

    2. Whenever you need an update, are your two workbooks opened or closed ? Two workbooks are Opened

    3. Do you actually need a comparison between the data held in both workbooks ? Yes

    4. With Unique Values in Column A, there is no need to worry about this column ... Yes or No ? Yes

    5. Are you looking for "a conditional copy" as soon as you update workbook 1 ? Yes


    If one row of values is added in the workbook 1(Employee(copy) then the one row needs to be copied to the next available row of workbook 2(Employee(Final))


    Please have a look at the attached target file in sheet2 tab where I gave some explanation Employee(final).xlsx


    Thank you in Advance

    Hello again,


    You can test following event macro:

    Hope this will help :)

    If you feel like saying "Thank You" for the help received, do not hesitate to click the "Smiley" icon, below, in the bottom right corner :)

    Edited once, last by Carim ().

    Attached are your two test files

    the destination file :

    Employee(final).xlsx

    and the Source file :

    Employee(copy).xlsm


    For your test procedure, once both workbooks are opened, just re-type every process, in Column E - from row 2 to row 9 ...

    Then check the Final workbook ...


    Hope this will help :)

    If you feel like saying "Thank You" for the help received, do not hesitate to click the "Smiley" icon, below, in the bottom right corner :)

    Love 1

    Thank you so much Carim, You are awesome!!


    only one thing, Now i can copy the data whenever the new rows get added to the source file, How can I also copy the rows which are already there? :)


    Sorry for asking you the multiple questions Carim.

    Glad to hear you have managed to sort it out ... :)


    Thanks a lot for your Thanks AND for the Like :thumbup:


    Regarding how to catch up for all previous records - assuming the Final Destination file is empty - two possibilities :


    1. Apply Autofilter in the Source file, make sure to properly add your criteria ( Date in Column C and Names Charles OR Nicolas in Column D) and copy all filtered rows to the destination file

    Or

    2. If you do not have too many records to go through, use the same technique as for your test, i.e. just re-type all the processes In Column E from the top row down to the bottom row of your source file


    Hope this will help ;)

    If you feel like saying "Thank You" for the help received, do not hesitate to click the "Smiley" icon, below, in the bottom right corner :)

    Love 1

    Pleased to help :)


    A Very Happy New Year to you too :thumbup:

    If you feel like saying "Thank You" for the help received, do not hesitate to click the "Smiley" icon, below, in the bottom right corner :)

    Love 1

    Hello Carim


    I hope you are doing well,


    I need your help man on adding some more features,


    In column G whenever the completed option is selected, the row must be copied to the another workbook,


    So here is the thing,


    1) Sometimes the status is changed from "Complete" to " rework" and once the rework is done then it will be again changed to completed but if I select the complete option after rework then for the second time again the same row is copied to the target file ,


    I would like to avoid adding the second time the same row to the target file :)


    2) I would also like to avoid duplicates in Column F


    Could you please help me hereEmployee(copy).xlsmEmployee(final).xlsx? :)


    Thank you in advance

    Hello Vengat,


    Below is your revised event macro V3 to take into account your remark about records already copied ...


    As far as your second question is concerned :

    2) I would also like to avoid duplicates in Column F


    Not sure to understand what you exactly mean ... is it just a sequence number ? how is it currently generated ?

    If you feel like saying "Thank You" for the help received, do not hesitate to click the "Smiley" icon, below, in the bottom right corner :)

    Love 1

    Hi Carim,


    Thank you so much mate!!


    Regarding the second question,


    so In column F - it is not a sequence number, it can be in a text and random numbers :)

    I don't need to have duplicates in column F, could you please help me mate? :)


    Thank you

    Hello again,


    When you say

    Quote


    I don't need to have duplicates in column F

    Do you mean :

    1. You want to prevent the User, whenever he is typing in Column F, from adding the same input as above ?

    or

    2. There are already duplicates records (entire rows) you do need to delete ?


    Thanks for your clarification

    If you feel like saying "Thank You" for the help received, do not hesitate to click the "Smiley" icon, below, in the bottom right corner :)

    Hi Carim,


    1) There are some duplicates records that i need to delete


    One last thing here if possible


    So there are two files, source(Employee(Copy).xlsm and target file(Employee(final).xlsx), With some conditions applied we are sending some data from source to the target file with the help of macros, I would like to check if all the data should be there in Target file is there.

    Could you please help here mate? the macros to compare the source file(Conditions applied) and target file, if some rows are missing in target file, those missing rows can be found in the another sheet of the source file.


    Thank you so much for helping me so far, it means a lot to me :)

    Hi again,


    Seems to me, you are moving away from your original question ...


    with two more topics ... which are quite independent one from the other ....


    1. Removing Duplicates


    2. Comparing the contents of two Workbooks


    Do we agree ? :)

    If you feel like saying "Thank You" for the help received, do not hesitate to click the "Smiley" icon, below, in the bottom right corner :)

    When it comes to the topic : Remove Duplicate Rows ... one has to be EXTRA CAREFUL ...

    and TEST the macro ONLY with a COPY of the Original file ...!!!


    You can test the following macro

    Code
    Sub RemoveDuplicateRows()
Dim rng As Range
    ' Remove Duplicates using Column F as the Reference Column
    With ActiveSheet
        Set rng = Range("A1", Range("F1").End(xlDown))
        rng.RemoveDuplicates Columns:=Array(1, 6), Header:=xlYes
    End With
End Sub

    Hope this will help

    If you feel like saying "Thank You" for the help received, do not hesitate to click the "Smiley" icon, below, in the bottom right corner :)

    Love 1

    Thanks for the code mate :)


    Regarding this one

    2. Comparing the contents of two Workbooks


    Do we agree ?

    Yes Carim, but I would like to compare the two files in the same conditions


    if there are some missing values in the target file then i would like to have those missing values in the another sheet of the source file for example (Sheet 2),


    Could you please help me with the last one thing here?


    Thank you in Advance :)

    Fair enough ... as soon as I have a moment will try to create a tailor-made comparison macro ... :)

    If you feel like saying "Thank You" for the help received, do not hesitate to click the "Smiley" icon, below, in the bottom right corner :)

    Love 1

Participate now!

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

Register Yourself Login