Hi, I wonder whether someone may be able to help me please.
With some help along the way, I've put together the script in the attached file which compares values from the 'Source' "AllData" sheet and extract these records, creating a unique distinct list before pasting them to specific 'Destination' sheets.
Although I have included explanations in my code for each section, I'll also try to explain in more detail below.
- The code initially looks for the following values in column E of the 'Source' "AllData" sheet, "DIR", "Enhancements", "IND" and "OVH".
- When the values "DIR", "Enhancements" or "IND" are found, the script then creates a unique distinct list and copies the data from column E of the 'Source' sheet and pastes this to column B of the applicable 'Destination' sheet (either "Direct Activities", "Enhancements" or "Indirect Activities"). In the case of the value "OVH" being found, the data from column D, rather than column E is copied and paste to column B of the "Overheads" 'Destination' sheet.
- In addition, the script sums a 'Manhour' figure from column I of the 'Source' sheet and places this under the relevant monthly column heading in the 'Destination' sheet.
What I'm now trying to do is amend this, and include the information from column B "LOB" on the 'Source' "AllData" sheet. So the script will now look and compare the values in columns B and E before creating the unique distinct list on the applicable 'Destination' sheet.
I appreciate that this may not be particularly easy to understand so I have attached my test file.
- The "Macro" sheet is fairly self explanatory. Please select the button to run the macro using the existing code.
- The "AllData" sheet is the 'Source' sheet where the data is extracted from.
- Sheets "Direct Activities", "Enhancements" and "Overheads" are the 'Destination' sheets for the current macro code.
- Sheets "Direct Activities Output", "Enhancements Output" and "Overheads Output" show how I would expect to see the results to be using the above change.
I've been trying for a few days now to make this this change, but I feel my lack of knowledge is hampering me in being able to achieve this.
I just wondered whether someone may be able to look at this please and offer some guidance on how I may go about achieving this.
Many thanks and kind regards