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!
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]
Last edited by Aussiebear; 01-31-2014 at 06:43 PM.
Reason: enclosed code with tags (Again)
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.
Posting Permissions
Reply With Quote