If I read this right, you just need to enter 157 as Price and CAD as Currency on the Dashboard sheet and then click on the Add button again to change the values.
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
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?
Yeh, it's a simple change to allow the PEG sheet to be selected from the Dashboard.
Updated version is attached.
My version is using autofilter which is relatively quick and efficient (and relatively easy to follow the code too I think).
There are lots of comments in the code so hopefully this is relatively clear.
Ok, I was assuming you wanted to paste the merged data to column A because that was in your original code as SOWRef.
It sounds like you want to leave column A blank, copy the merged data to column B and then the remaining columns after that.
A minor tweak should fix that. Assuming you still have the SOWref constant = "A" then the attached should work.
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.
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.
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.
I am not seeing code in what you provided already that merges these columns.
Can you please just confirm what you mean by merging the data.
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.
try leaving out the first comma ie:
JumpToNextCtl ws, ctlGrpName, activeTbx
JumpToNextCtl, ws, ctlGrpName, activeTbx
sounds like the cells are using a custom format
maybe select one of the cells that has this formatting and press Ctrl + 1 key combo to get the format menu and see if there is a custom format
As per Carim's suggestion, it is always best to provide a sample sheet.
There are numerous ways to handle this sort of task, however I think filtering and copying seems appropriate.
So based on the information and code you've provided, attached is one way to achieve what I think you are after.
That's because the code loops through the worksheets:
If you only want it to work on the "pandp" sheet just change it accordingly.
Revised example attached.
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.