Create delivery request upon button click

[Baziwan]

Hi All,

I want to have a button on my excel document that when pushed will automtically create a delivery request for products.
If the quantity drops below the minimum stock and there is stock available in the warehouse, it would add the product name & rotation number to the delivery request form.
Also it would recommend a quantity to order based on the difference between the maximum stock holding and the actual stock in the store.
I've attached a dummy document so that you can see what I'm after.

How can I achieve this?

Any help would be much appreciated

Thanks in advance

[Leith Ross]

Hello Baz,

After having a look at your workbook, I am uncertain about what you want. There is not much to work with. It probably seems clear and easy to understand to you because you are very familiar with the process. As for the rest of us, we are not. You should provide more detailed "before" and "after" explanations. Even if a point seems obvious or trivial, you should include it.

[Baziwan]

Hi Leith,

Sorry about the lack of detail. I've attached another example with more details.

[Leith Ross]

Hello Baziwan,

This macro works correctly on the sample workbook. The code is written to allow for 1 or more warehouses. The warehouse name must contain the word " Bond " in the row 1 headers. The macro also determines the last entry made in the database. Your table can grow and the macro will not need to be changed. Test this on a copy of the real database and let me know how it works. Here is the macro that has been added to the button on the "Database" worksheet.

Please Login or Register  to view this content.

[Baziwan]

Hi Leith,

That's great, it's almost exactly what I'm after. Is it possible to make a few changes. Firstly, I only need products that have dropped below the minimum stock value to be added to the delivery request sheet & secondly, I already have a macro for goods received that deducts from the bond column and adds the amounts to the store quantities when it is booked in. Everything else is great tho, so can it be modified so that it doesn't change the values on the database sheet & only enters details of stock that has dropped below the minimum stock holding? Sorry, I should have mentioned these in the details I provided.

Thanks for your help with this Leith, your time is very much appreciated!

[Leith Ross]

Hello Baziwan,

I made the changes you asked for and they have been added to the attached workbook. Here is the updated macro code.

Please Login or Register  to view this content.

[Baziwan]

Excellent, thanks very much Leith. I'll mark this as solved.
I wonder If I could pick your brains regarding another problem.
I mentioned that I'm using another macro to deal with goods coming in from an external warehouse. It's set up for that warehouse having stock under two different rotation numbers like the file attachment for this. However, recently the rotations have increased and it could be as many as up to 6 different rotations and I'm unsure how to adapt my code to deal with it. If I post it under a new thread, is there any chance you could take a look at the code for me?

Thanks again for your help with this!!

[Leith Ross]

Hello Baz,

You can add as many new warehouses as you want. The only criteria are the warehouse header must contain the word " Bond " (with the spaces) and the first column to the right must be the rotation number, and the second column to the right must have the received date.

[Baziwan]

Hi Leith, that wasn't what I was referring to. I'm referring to a macro that transfers the stock from the bond quantity to the store quantity when the delivery arrives. I'll post it under a new thread titled "Transfer values".