Automated Inventory Report

[legendarylalo]

Cross-posted at:
https://www.reddit.com/r/excel/comments/xz9cmf/vba_inventory_report_project/

Hi guys,

I was hoping i could get some help from some VBA experts. I have been working on a project automate an inventory/order management excel spreadsheet. Attached is a very basic report with a few SKUs. If I can automate this report it will replace having to this manually, which usually takes a full day. There are thousands of items and materials.


In the Paste-Inventory sheet you will see some manual calculations.

What I want to do is make it to where it counts my inventory I have on hand, start with the first ship by date. FIFO(First in First Out). The following are criteria's

1. If item is in storage location 5 then it can't be used. So has to be skipped.
2. If an item is on Hold LOST or out of SPEC, then it can't be used.
3. If any item is past ship by date, then it can't be used.
4. Nothing in blocked counts, so we can just take calculations from unrestricted cases/pounds.

I wanted to get the highlighted, but the orders "Good issues Date: Must be before(Can't be on) The ship by date. To explain, if ship by date is 10/08 and goods issue date of order is 10/08 we can't use that product, so can't count it. Also if I have 100 cases of inventory for an item. It will need to catch all orders from that location, before moving to the next batch in order of ship by date. These items must ship by that date or they can't be used anymore.

What makes it hard is that you must take your inventory with the closest ship by date first and match it with the location. (Location is where inventory is at and Order location is where they are ordering it, so it must match.


Any ideas, what approach or how I can do this?


Capture
Capture2