Stock Count Sheet

I have made an excel workbook to monitor the amount of stock held in our parts room. Sheet 2 includes part numbers, part descriptions and quantities in store room. Sheet 3 records the person who takes an item including the quantity he is taking and the part number. Every time i take an item out on sheet 3, i want that to deduct from the same items quantity on Sheet 2. There will be on sheet 3 serveral lines that may repeat the same part over and over.

I need a VB code that will do this job for me, unless there is a simple way of doing it with Excel funtions.

Any help would be appreciated

It'd probably be easier with worksheet functions. Use sumif to subtract the total amount on sheet 2 from sheet 1.

I don't see where your available stock is stored on your posted spreadsheet; it doesn't look like what you originally described. There are no total quantities, and none of the parts in sheet2 have an associated quantity.

See the workbook I've posted. I changed your Qty column on sheet2 to hold the original qty, and then created a new column to hold the remaining qty. Will this work for your purposes?

If you'd prefer to change the Qty column instead, you'll probably want to use a macro. You'll need something to trigger the macro to run. How would data be entered into sheet 3?

I have created the following coded command button that i have put on sheet 2

Private Sub CommandButton1_Click()
    
    Dim paste1 As Range
    Dim Copy1 As Range
    Set Copy1 = Range("M7:M200")
    Set paste1 = Range("I7:I200")
    
    Dim Copy2 As Range
    Dim Paste2 As Range
    Set Copy2 = Sheet3.Range("B7:J23")
    Set Paste2 = Sheet3.Range("L7:T23")
    
    paste1.Value = Copy1.Value
    Paste2.Value = Copy2.Value
    Copy2.Value = " "

End Sub

The only problem now is that everytime i press this button it will overwrite "Paste2"'s cells each time, wiping what i copied previously. Do you know what the formula would be to check if a cell is empty or not, and if it is not then keep going into an empty cell is founf and then pasting "Copy2" there instead?

Hi mgwillimk

Please take a few minutes to read the Forum Rules, then edit your previous post to include code tags. Once the tags are added we will do our best to help you find a resolution to your issue.

Thank You

Dave

I guess I'm a bit unclear on what you are trying to accomplish. What is your above code supposed to do? You already have two sheets that seem to function how you asked. Why do the copy/paste and clear?

(BTW, putting spaces in cells to clear them is a bad idea. Instead, just make the cells blank with .ClearContents)

This should work better:

Private Sub CommandButton1_Click()
    
    If MsgBox("If you have ordered up a replacement part then Press OK, if not then press cancel.", _
        vbOKCancel, "Order replaced?") = vbOK Then
        Sheets("Sheet2").Range("I7:I200").Value = Sheets("Sheet2").Range("M7:M200").Value
        Sheets("Sheet3").Range(Sheets("Sheet3").Cells(7, "B"), Sheets("Sheet3").Cells(Sheets("Sheet3").Cells(Rows.Count, "J").End(xlUp).Row, "J")).Copy Destination:=Sheets("Sheet3").Cells(Sheets("Sheet3").Cells(Rows.Count, "L").End(xlUp).Row + 1, "L")
        Sheets("Sheet3").Range(Sheets("Sheet3").Cells(7, "B"), Sheets("Sheet3").Cells(Sheets("Sheet3").Cells(Rows.Count, "J").End(xlUp).Row, "J")).ClearContents
    End If
 
End Sub