Posts by gijsmo

    Best to specify as many requirements in one go in order to minimise re-coding.

    The assumption based on the original sample was that the macro had to :

    1. Autofilter PEG sheet on OP, DP, d_type

    2. Find the number to be matched column (eg, 35037)

    3. Update the number to be matched column with price & the adjacent column with the currency

    4. Update the transit_time, expiry & effective_date columns with data from the Dashboard sheet

    All the columns were located using a Range.Find method, if one or more of the columns on the PEG sheet were missing, nothing would get updated


    So :

    1. Is Transit Time the only column that may or may not exist on the PEG sheets ?

    2. Will the column names on the other PEG sheets be the same ? eg OP, DP, d_type, etc ?

    Just remember for this to work on anything other than the PEG_3 sheet, the other PEG sheets need to have the columns called OP, DP, d_type, transit_time, expiry and effective_date

    The macro won't do any updating if these columns do not all exist

    Just a question - it would be easier to be able to copy the "template" sheets as is if there were no formulas in them as you've requested to copy without formulas. It is feasible to copy without formulas but is there some reason the formulas are in the template sheets if they are not going to be used in the workbooks to be created?

    OK, as with many things in Excel, there are a number of ways to do this.


    In the attached example, I have chosen to create the merged data in a helper column to the right of the existing data (this will be temporarily created by the macro in the source sheet).

    The only small complication with this is that the data in the helper column needs to be pasted first into the SOW sheet and the assumption is the remainder of the columns can be copied together after this merged data.


    Please note, there are a number of small changes and corrections to the code in addition to accommodating this merged data so need to copy this carefully to your project.


    Copy & Paste Different Columns v2.xlsm

    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

    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

    I'm not quite sure what you mean by "I’ve noticed the dates must be sorted first for it to be effective otherwise it only copies data over up to a point."

    Auto-filtering should not need data to be sorted first. I think you'll need to provide more information about what you're seeing or perhaps another example workbook as the sample one only has a very limited number of rows.


    And, yes, you can of course cut & paste to the new workbook and remove the "empty" rows with more code.