Copying only specific data from one worksheet to another based on a cell's value

    Thank you gijsmo! This gives us a list just as we want, however, is there a way it wouldn't be recreated each time it is run, but any new data added to it? Once we have the list created, we need to add more data from other sources, which has to be done manually. I don't want to re-add that additional data each time I run the macro.

    Yes, you can of course append data to an existing NCR sheet if this is required.

    As it wasn't specified in your original request it was 'safer' to recreate the sheet each time.


    The attached revised version will append matching data from the 2021 sheet to the end of the NCR sheet. The danger here is that if the macro is run more than once with the same data in the 2021 sheet, the same data will be appended to the NCR sheet.


    If you only wanted to add data that is not already on the NCR sheet then that is a different level of complication but maybe not impossible to overcome. The easiest way would be to remove duplicate rows after the copying is completed however I notice you already seem to have duplicate data in the sample sheet eg, Lot numbers MS20J29248, MS20L14745, MS21A07416 etc. So you'd need to determine what data you actually want on the final NCR sheet after copying data, particularly if you run the macro more than once.


    Reference 2021 Active Log Book v2.xlsm

    Hi gijsmo,


    This document is a tracking tool when document control issues a manufacturing batch record. It is used daily and data is added throughout the day as they issue batch records. This data is added on to the "2021" sheet. On occasion, there is a Non-Conformance to the batch record, and we log that information in the NCR column. We would like to capture the list of NCRs and Lot Numbers on a separate sheet. Once they are listed on that separate sheet, we have to go into a database and manually copy the reason for that Non-Conformance, and paste it into another column on the NCR sheet (the reason for the NCR can be quite lengthy). I would like to have the Lot Number and NCR columns copied onto the NCR sheet from the 2021 sheet (as you have provided). Once that is done, we will add our comments. Then, as we add more NCR data onto the 2021 sheet, append the new NCR data that we add onto the NCR sheet, without changing the list that is already on the NCR sheet, then we will add the database comments to the new data. Is that possible?

    I appreciate what you are trying to do and with the 2nd version of my code it will now allow you to append data to the NCR sheet which partly satisfies your request.


    However, as it stands, if you run my macro code more than once it will append all matching row data to the NCR sheet. This means it will currently create duplicate rows. I thought that maybe the way around this was to remove duplicates after the data was copied, that way it could eliminate data already copied to the NCR sheet.


    As per my previous post, the problem is I have noticed there is already duplicate data in the sample sheet you provided eg, Lot numbers MS20J29248, MS20L14745, MS21A07416 etc and each have the same NCR value. So, of the 48 entries with an NCR, there are actually only 35 unique values.


    Just looking at the data, there is no obvious 'unique' identifier for these duplicates eg:


    Once you add comments it will actually make it harder in some respects to prevent duplicate data from being added to the NCR sheet.


    One possible relatively simple solution I have attached is to add another column in the sheet which I've named "Copied to NCR sheet" (column V). If there is a "Y" value in that column, it means the row has already been copied to the NCR sheet, therefore running the macro multiple times will not cause duplicate rows to be copied. After the macro successfully copies new rows to the NCR sheet it will mark those rows with a "Y" in the "Copied to NCR sheet" column (and they won't be copied again). So as long as you are OK with another column in the sheet in this location, this should work for you.


    Reference 2021 Active Log Book v3.xlsm

    This is perfect!! Yes, there are duplicates, but they are actually separate documents that we track, so I'm pleased that you didn't add a step to remove them. The extra column is not a problem.


    Does the macro identify with the filename? The file you've been updating is a copy of the original, so I'd like to copy over the data we've added from the original to this file and rename it.


    Thank you so very much. Love this site and all of you are so helpful...it is greatly appreciated!

    You are welcome !


    The filename is independent of the macro so you can copy over whatever data you wish and rename the file without any issues.


    Just remember there is now an extra column in my copy of the file.

Participate now!

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

Register Yourself Login