Help! Need to move rows to separate worksheet if cell values match a value in range

I've browsed through the posts on this forum before and found a lot of other macros that have considerably eased my workload, so first off- thank you. I'm also hoping to get some assistance with a new macro that I'm writing (workbook attached.)

To summarize, every month I do a massive data pull and dump a ton of unsorted information into a worksheet. Based on groups of product SKUs that belong to each of the "Packer", "Operator" and "Distributor" categories, I move each row of data into one of three other worksheets of same names. If a new product SKU appears, I have to determine what category it belongs to before I move that row to the appropriate worksheet.

There are about ~1000 rows of data so, as you can imagine, this takes up a huge amount of time every month

What I would like to do is build a macro that automates the process; i.e. moves a row to the "Operator" worksheet if the cell value in the "Product SKU" column matches one of the values in a named range of Operator product SKUs, to the "Distributor" worksheet if it matches one of the values in a named range of Distributor product SKUs. It would also be great if the named ranges were re-defined when I added a new SKU.

Can anyone provide some guidance? I've hit a roadblock as I'm not sure how to move based on this type of criteria and would really appreciate some help!

This code ran without error when tested on your sample worksheet/book, although I did limit the loop iterations by deleting some cells on the Category Product SKU sheet.

Note: there is no list for Packer Category SKU, and I assumed this list will follow the same pattern & column separation as the Distributor and Operator lists.

This code could be improved and there is no error handling built in.

Please Login or Register  to view this content.

The code was written based on your sample workbook, in which it ran without error.

As I said, there is no error handling built in - it's just the essential code.
If sheet5 and sheet6 do not exist in the real workbook then the code will fail.

References to sheet5 and sheet6 are sheet code names. You may need to adjust these references if that is where the code is failing.

Mistake on my part. Since your original workbook did not contain criteria for Packer, I had to set the array variable to (2).

Near the top of the code on the "Dim" line, change the Sh(2) to Sh(3),

After changing this, using your last sample workbook, the code ran to completion w/o error, although there were no values copies to the Packer sheet - a manual check with match formula conforms no Packer SKU exist in the source sheet, which confirms the code did not miss any of them.

BTW, the code should go into a standard module - not in the Thisworkbook module.

Adding the Macro

1. Copy the macro above pressing the keys CTRL+C
2. Open your workbook
3. Press the keys ALT+F11 to open the Visual Basic Editor
4. Press the keys ALT+I to activate the Insert menu
5. Press M to insert a Standard Module
6. Paste the code by pressing the keys CTRL+V
7. Make any custom changes to the macro if needed at this time.
8. Save the Macro by pressing the keys CTRL+S
9. Press the keys ALT+Q to exit the Editor, and return to Excel.