Previous Topic Next Topic

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



Related:

2 responses

Answer 1 / 2
TrowaD Posts 2921 Registration date Sunday September 12, 2010 Status Moderator Last seen December 27, 2022 552
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
Ashley
Mar 11, 2013 at 01:36 PM
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
Ashley
Mar 12, 2013 at 08:59 PM
Still doesn't work
Maybe its because Office2010?
0
rizvisa1 Posts 4478 Registration date Thursday January 28, 2010 Status Contributor Last seen May 5, 2022 766
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
Ashley
Mar 17, 2013 at 09:00 PM
Thanks a lot Trowa. It works
0
Answer 2 / 2
TrowaD Posts 2921 Registration date Sunday September 12, 2010 Status Moderator Last seen December 27, 2022 552
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