VBA to read data and adapt to new template

[cwyenberg]

On the attached spreadsheet example, you will find the following:

1. Sheet "Data" - this is the original data imported from a .txt file.

2. The desired output is found on sheet "calell" (minus the red explanation and blank rows)

3. To achieve this I need a script that will look in sheet "Data" column "PKG_CUSTOM2" for the values in sheet "CRI_SKUS" column "MERCH_SKU".

4. If found, the corresponding values in sheet "CRI_SKUS" columns B, C, D & E must then be placed in the corresponding (purple) cells of sheet "calell"

Essentially what happens is I get orders with one sku number that actually represents an assembly of three or four items. These single sku numbers must be broken down for the warehouse/shipping dept. for picking purposes.

5. Colour coding in sheet "calell":

green - these cells are static and will have the same values all the time (note column F has the TODAY() formula)

blue - these cells are representative of values from the sheet "Data"

purple - values drawn from sheet "CRI_SKUS"

6. Finally the sheet "calell" must be exported as a csv file.

Hopefully I've been clear enough in the request.

Thanks in advance.
Clint

[event21]

Hi -

try;

Please Login or Register  to view this content.

event

[cwyenberg]

Thanks event,

Close, but needs some tweaking. I've attached the excel file again with the results of your script on the "calell" sheet. Please note columns AB thru AE your results are in yellow and the desired results are in orange.

Thanks,
Clint

[event21]

Hi -

change

Please Login or Register  to view this content.

to

Please Login or Register  to view this content.

event

[cwyenberg]

This seems to work, thanks!

one last thing (I hope)...what do I add to your code to clear the cells on sheet "calell" prior to executing the remainder of the script? I'd like to leave row 1 unaffected and leave all formatting and formulas in place

[event21]

Hi -

in the top of the codes

Please Login or Register  to view this content.

event

[cwyenberg]

Excellent. Many thanks, event!

[cwyenberg]

event,

I need to add a column "WEIGHT" on sheet "CRI SKUS" as indicated on the attached file. In the same way as the other columns, this data needs to be read into column AG (colored in red) on sheet "calell". I've looked at your code and confess I have no idea as to how to accomplish this

Thanks,
Clint

[event21]

Hi -

change from

Please Login or Register  to view this content.

to

Please Login or Register  to view this content.

event