OK, I made code that updates the Inventory sheet. I made it generic so it should work for any Product (and this code is re-usable for all Products).
So, if you have 4 Products (and 4 Product sheets, one for each one), here is how you would set it up.
First, here is the general code to update the Inventory sheet:
VBA Code:
Private Sub ReduceInventory(wsPrd As Worksheet)
Dim wsInv As Worksheet
Dim lrP As Long
Dim lrI As Long
Dim rP As Long
Dim rI As Long
Dim cat As String
Dim des As String
Dim qty As Long
' Designate Inventory worksheets
Set wsInv = Sheets("Inventory")
Application.ScreenUpdating = False
' Find last row of data on Product and Inventory sheets
lrP = wsPrd.Cells(Rows.Count, "A").End(xlUp).Row
lrI = wsInv.Cells(Rows.Count, "A").End(xlUp).Row
' Loop through all rows on Product sheet, starting with row 2
For rP = 2 To lrP
' Get Category, Description, and Quantity from row
cat = wsPrd.Cells(rP, "A")
des = wsPrd.Cells(rP, "B")
qty = wsPrd.Cells(rP, "C")
' Loop through all rows on Inventory sheet until you find the match
For rI = 2 To lrI
' See if columns A and B match
If wsInv.Cells(rI, "A") = cat And wsInv.Cells(rI, "B") = des Then
' Subtract quantity from column J
wsInv.Cells(rI, "J").Value = wsInv.Cells(rI, "J").Value - qty
Exit For
End If
' Check to see if not found
If rI = lrI Then
MsgBox "Cannot find entry for " & cat & "/" & des & " on Inventory sheet", vbOKOnly
End If
Next rI
Next rP
Application.ScreenUpdating = True
MsgBox "Inventory update finished!", vbOKOnly
End Sub
Then, we want the section of code that calls this code, feeding in the appropriate Product sheet.
So, for the PCA sheet, the code to attach to your button would look like this:
VBA Code:
Sub ProcessPCA()
' Call ReduceInventory code and pass "PCA" sheet reference
Call ReduceInventory(Sheets("PCA"))
End Sub
And you would have similar procedures for the other products, just changing the procedure name and sheet name in that code above.