Hello,

I have create an invoice workbook call POS and every invoice no will be saved as sheets with their respective invoice no.
Product informations are link from another file call STOCK. I use Vlookup to paste the product info into invoice.

Now I would like to add a code such that every product sold on the invoice will have the invoice no. and qty copied into the file STOCK. Currently I am doing a manually stock taking which is very time consuming.

Here's the placement.
Invoice no is in M5. Every invoice will be saved as a worksheet.
Product No. is in D21-D40 Qty is E21-E40.
Exchanged from Inv is I21-I40 and J21-J40 is the returned amt.

In Stock.
Product Code in Column E
Ordered Qty is in Column G
Qty available is in Column H
Total Qty sold is in Column I
Sold Invoices are Column J,K,L onwards are for the Invoices sold. The next available blank cell.

I would manually open this 2 files and look thru every sheets.

If Invoice No 108 ( CELL M5)
sold 3 qty of product no 163.
sold 4qty of product no 27-1
In the Stock Workbook, I will find the ROW where Product no 163 is and manually keyed in the invoice no. the cell in column J (108),column K (108) and column L (108). Then Total no. of 163 sold so far will be in column I. Next I will do the same for product 27-1 and keyed into the next available cell in the sold invoices.


If Invoice No 109 (CELL M5)
1 qty of product no 163 sold in Inv 108 (CELL I21). J21 will the returned amount.
1 qty of product no 88-1
customer wants to exchange 1 qty of product 163 to 88-1.
In this case i would manually update the stock by going to Product 163 Row to delete one of the cell containing 108. And go to Product row 88-1 to add in invoice 109 indicating one qty sold.

Can someone please help me to write this marco code. Or does anyone have a better solution?
Basically I am trying to cut away from manually update stock which alot of time and human errors involved.
Thanks in advance.Configuration: Mac OS X Safari 525.28.3