Attempting to auto update and sort an inventory list - need help

Hello,

I am trying to help a family business out. I am attempting to do in Excel what should be done in a database program. I need to find a way to have different sheets in Excel match the master sheet and sort by part number (STK Code) one sheet for each type of part. Each part number has a prefix eg. 01-3732 or 10-64873. I am attempting to create a sheet for each prefix 01 - 11 and a master list.

I am trying to find a way to make it so when they enter something in the master list, it also puts it in the correct sheet for the part that is it. I have tried both vlookup and pivot tables, but I have never attempted this before and have no clue where to begin. I have tried a few macros that I have found around on the forums, but none of them seemed to work for my situation. I am most likely doing something very wrong.

I have attached a sample of what they want it to look like.

I know Excel is not exactly the best option for this, however this is what they want and they dont want to learn a database program.

I would appreciate any and all help you could provide.

Place the following code in the worksheet code module. Right click the "Master" tab. Click 'View Code'. Copy and paste the macro into the empty code window that opens up. Close the window to return to your worksheet. The critical point that you must remember is that the appropriate data will be copied automatically to the right sheet when you enter the 'Stk Code' and then exit that cell. This means that the 'Stk Code' must be the last item entered in each row, so enter all the data and leave the code until the end. You could move the code column (currently column B) to the end so that it is the last item entered, but if you do this the Range("B:B") in the code must be changed to Range("M:M"). Please let me know how it works out.

Please Login or Register  to view this content.

Thank you so much! So far it is working great. Just a few questions though...

1) If they have to change something, for example quantity, even if I edit the quantity and then go into STK Code and make sure that is the last thing entered, will it not update the change?

2) Do I have to go through the entire list STK Code by STK Code and press enter to make it show up on the sorted list? There are a staggering amount of rows.

Awesome! Thank you very much! You have done in 1 day that I have been trying to do for 2 weeks

It was my pleasure.