Macro to convert Supplier order from Master Build List.

Hi Everybody,
I'm brand new to this forum so I'm not too sure if it is the right place to post this "How to" question but this is what I'm looking for.

The company I'm working for has to place parts order every so often and it's never done right. Each product we sale has X number parts that we order from Y number of suppliers.
Each product has an Excel Master build list, with all the part we need (including part #, description, supplier name, cost, quantity per product and so on).
What I would like to create is an Excel file that would take the information for the master build list, create a new sheet for each supplier and that would create a list of all the part that each supplier has to supply for us (including quantities, description and so on). Also, I would to be able to enter how many quantity of this product we would like to order for this month so the quantities in each "Supplier" sheet would be exactly what we want.

I'm not sure if I explain it correctly but if I didn't and you don't mind helping me out, do not hesitate to ask me question.
Btw, I really don't know where to start and I did had an advance Excel training too many years ago but I never practice it so I'm just a basic user.

Thanks in advance and I hope you can shine help.
Seb

This is quite simple I think.

But you will need to change your title to something like:

Macro to convert Customer order into Supplier order from Master Build List.


Then post examples of your customer order list and your master build list.

Attach a sample workbook. Make sure there is just enough data to demonstrate your need. Include a BEFORE sheet and an AFTER sheet in the workbook if needed to show the process you're trying to complete or automate. Make sure your desired results are shown, mock them up manually if necessary.

Remember to desensitize the data.

Click on GO ADVANCED and then scroll down to Manage Attachments to open the upload window (or use the paperclip icon).

Alright, so the first picture is the master list.
The second one (called Sheet 1) is the final result for supplier #K001.
The third one (called Sheet 2)for supplier #P001, and so on....
I didn't do all of them but think it explain the idea.
The adjustment colum is if we already some of this part in stock (-x) or if we have some missing to complet an order (+x).

"March" run quantity is how many of this product we would like to build in March.

If I change supplier for an existing part, I'd like the supplier sheets to update by themself and if I add a new part with a new supplier I'd like to supplier sheet to be created by himself to (if Excel can).

Is anyone can help?
Thansk again.
Seb

PS: Never thought I could be that excited about Excel.....

Just in case the file help....

Are the Master Build Lists in Separate workbooks or would they all be in the same workbook?

What will the master build file names be like?

They cannot all be called Master Build.

Ok

Run this Macro.

Please Login or Register  to view this content.

Hi,
First, thank you very much for your help, I wasn't expecting that much! You are an AAA grade type guy.

I tried the macro and it worked fine until I added a line (#31), then when I ran the macro again the quantities wouldn't update anymore. By adding a line I change the location of "March run quantity", the quantity update themself but not when I ran the macro. So, where in the macro can I change D$15 to D$16 or a different location? I can't figure it out.....

Thank "Again"!
Sebastien

My Apologies

I thought I had fixed that.

Please Login or Register  to view this content.