Help Subtract value and update cell in another workbook

[bhoon]

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!

11182

[Pasi12]

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

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

[bhoon]

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

[Pasi12]

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.

[bhoon]

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?

[Pasi12]

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

[Pasi12]

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