Inventory

[Solved/Closed]
Report
-
 Ashley -
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

Posts
2805
Registration date
Sunday September 12, 2010
Status
Moderator
Last seen
September 14, 2021
481
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
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
Still doesn't work
Maybe its because Office2010?
Posts
4476
Registration date
Thursday January 28, 2010
Status
Contributor
Last seen
August 2, 2020
768
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
Thanks a lot Trowa. It works
Posts
2805
Registration date
Sunday September 12, 2010
Status
Moderator
Last seen
September 14, 2021
481
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