Inventory

Solved/Closed
Ashley - Mar 10, 2013 at 08:02 PM
 Ashley - Mar 17, 2013 at 09:00 PM
Hello,
Can you pls help me with the following:

I have Inventory Workbook, and Invoices.
Every time I open new Invoice and entering Quantities of items, I need it to be subtracted from Inventory (remember pls, every time it is new Invoice)
Thanks



2 replies

TrowaD
Posts
2900
Registration date
Sunday September 12, 2010
Status
Moderator
Last seen
September 12, 2022
523
Mar 11, 2013 at 12:46 PM
Hi Ashley,

Are your Inventory and Invoices in the same workbook or many different ones?

What is the moment you want to subtract from inventory?
When you enter the amount? This means no mistakes can be made or more will be subtracted.

Best regards,
Trowa
0
Hi Trowa,
Yes, I have a file- Inventory, and many different files of Invoices.
I need the item from Inventory to be subtracted after I create the new Invoice and save it.
Thanks
0
Still doesn't work
Maybe its because Office2010?
0
rizvisa1
Posts
4479
Registration date
Thursday January 28, 2010
Status
Contributor
Last seen
May 5, 2022
768
Mar 16, 2013 at 12:20 PM
Nothing to do with office 2010. have you followed TrowaD directions given at https://ccm.net/forum/affich-692426-inventory#3 to the teeth ? May be if you can upload a sample file one can see where the issue is
0
Thanks a lot Trowa. It works
0
TrowaD
Posts
2900
Registration date
Sunday September 12, 2010
Status
Moderator
Last seen
September 12, 2022
523
Mar 12, 2013 at 12:06 PM
Hi Ashley,

I'm guessing you have a template for the invoices.

Paste this code under ThisWorkbook in the template:
Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
Dim aW As Workbook
Set aW = ActiveWorkbook
Workbooks.Open Filename:="C:\Documents\Inventory.xls"
Range("A1").Value = Range("A1").Value - aW.Sheets("Sheet1").Range("A10").Value
Workbooks("Inventory").Close savechanges:=True
End Sub

Find the text C:\Documents\Inventory.xls and change it to match your situation.

Now when you save your invoice template and giving it a new name (Save as...), the inventory file is opened and the stock in A1 is adjusted according to the data in A10 from the invoice template.

Remember that each time the invoice is saved, the code will run and inventory wil be adjusted.

Best regards,
Trowa
0