Deplete cell values based on a common reference 2

[victoria2207]

Good Afternoon,

I posted a similar query last month and got a great response, I now have to expand upon the idea.

Basically, I have a stock sheet and every time a product is created the stock for it must be depleted manually a part at a time. We have almost 8000 parts and each product can consist of up to 500 different items.

I have created a monthly stock sheet with a column for each day of the month OR product created:

			Date Created
			Work Order
			Name
Part No.	Supplier Part No.	Description	TOTAL Outbound	Item 1	Item 2	Item 3	Etc
AAA	6704	Washer	0
BBB	10039	Clamp	0
CCC	10612	Mount	0
DDD	10617	Filter	0
Etc			0

<colgroup><col width="64" style="width: 48pt;" span="2"> <col width="141" style="width: 106pt; mso-width-source: userset; mso-width-alt: 5006;"> <col width="64" style="width: 48pt;" span="5"> <tbody> </tbody>

What I think I would like to be able to do is when a material list is dropped into a sheet (Say the 'work order' sheet), at the top of each column have a button which runs a macro which will look up to that sheet for the values (which will have the part number in common) and drop the values into that particular column then copy/paste them as values.

There may be an easier way to do this, including an individual sheet for each product (and I can simply do a sumif) but this has 2 problems:

1. The file will be massive - this is already a problem with the current stock file which has to be repaired on average once a month
2. The other people using it are not as familiar with Excel as me (and I am far/far from an expert) so asking them to enter formula without causing an error somewhere is far from ideal.

If anyone has any ideas/examples of something that already exists that fits this purpose - I would be very greatful.

Thanks in advance,

Vic2207

 

[victoria2207]

Using the VBA provided to me in my previous thread and tailoring it, I have managed to resolve this.
Thank you to anyone who considered this query.