Stock Count Sheet

[mgwillimk]

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

[davegugg]

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

[mgwillimk]

Thanks davegugg, i don't suppose you could provide me with a sample function line - i am still a basic in excel

[mgwillimk]

I have attached a copy of the file i am trying to make.

[davegugg]

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.

[mgwillimk]

Apologies, i saved the file incorrectly. I have rectified this and am now uploading the most recent version. Annoyingly it is not uploading properly at the moment and will try again in a while

[mgwillimk]

Here is the file. It is a basic version of what i am doing but should suffice to play with

[davegugg]

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?

[mgwillimk]

It looks brilliant. I've tried every which way of sums and ifs but did not know it could be done like that! The data would be entered manually. What could work is if i had a command button that copied column (m) to column (I) on Sheet2, and then clear the list from Sheet3, copying it to maybe a fourth sheet so that i keep the data, but as it has moved it no longer affects the numbers. Do you think this would be possible? Thank you again for the help you're giving me

[mgwillimk]

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

Please Login or Register  to view this content.

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?

[davegugg]

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

[mgwillimk]

here we go, code tagged

[davegugg]

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)

[mgwillimk]

I'm attempting to just tidy up the page a bit. Also the aim is that when i have ordered up items i would like to remoove them from the list on "Sheet3" - i don't mind if they stay on the same sheet or move to a new one, but obviously the code i wrote above will overwrite the same cells over and over again, losing previously copied information. I need a code that will find out if a cell is a blank or not, and if it is not blank, skip to the next blank cell beneath it and THEN copy it so i don't lose any information. I have been playing with a loop that checks if a cell="", and if not then skip down to the next cell until it finds the blank cell and pastes the information. Unfortunately whilst the code seems ok, i end up in an constant loop...

Please Login or Register  to view this content.

[davegugg]

This should work better:

Please Login or Register  to view this content.

[mgwillimk]

Thanks again for all the help, this does work except for one thing - it copies, pastes and wipes the headings as well (row 5) but i only need it to start from row 6 or 7. I've tried altering the code but it doesn't want to work... but other than that it is perfect for what i need

[mgwillimk]

I've played with this a bit and it is now perfect, thank you for all your help with my issue, it is much appreciated.