Help Subtract value and update cell in another workbook

I am creating an product order form (book 1, B2). when i enter in a quantity of ordered product, i would like to have a macro that will subtract that value from the inventory workbook (book 2, E2). But i want the new inventory amount to be updated and not moved to another cell. HELP THIS IS DRIVING ME NUTS!

Attachment 11182

Hello Bhoon;
Try this and tested it. Assuming you have 2 different book like book1.xls and book2.xls. the below code will open each book , subtracts the inventory and update the inventory in the same cell on book 2.
hope this helps.


Note: It your data has decimals then change this line

Dim lngDiff As Long
to
Dim lngDiff As Double

Pasi.

Book 2
[CODE]Sub SampleInventory()
Dim wb1 As Workbook, wb2 As Workbook
Dim lngDiff As Long
On Error GoTo Err
Application.ScreenUpdating = False
Set wb1 = ActiveWorkbook
Set wb2 = Workbooks.Open("C:\Users\USRENAME\Desktop\wb2.xlsx") ' inventory bood
Set wb1 = Workbooks.Open("C:\Users\USERNAME\Desktop\wb1.xlsx") ' Quantity order book
lngDiff = wb2.Sheets("Sheet1").Range("B2").Value - _
wb1.Sheets("Sheet1").Range("B2").Value
wb2.Sheets("Sheet1").Range("B2").Value = lngDiff
' wb1.Close savechanges:=False
' wb2.Close savechanges:=False
Application.ScreenUpdating = True
Exit Sub
Err:
MsgBox Err.Description
End Sub[/CODE]

i tried it out, for some reason i can only run the macro once. i put the same values in but when i ran it the new inventory level returned a value of -625

Make sure your WK2 is the inventory and point your path to that book. I think your setup of your current inventory book and Quantity ordered are swapped. That's the only way you get the "negative" sign.

i cant get it to work...do the file paths need to be .xlxm?
could you possibly post the books you were using as tests?

Yes they are .xlsx. Here is the first file make sure you change the file names in the path to match the attached files.

Attachment 11196 second file. Let me know if this works!
Pasi.