Item distribution on different tray

Hi everybody, I'm not really expert of VBA, but I hope someone could help me with a MACRO that could solve my issue.

The target of my macro would be to divide the item quantity of a bill of material in different trays and slots.

In my uploaded excel file I wrote data and expected answer of the macro.

In the sheet "data" I have the bill of material with list of Item and quantity that I have to distribute/divide on several trays.
In the sheet "tray type" i have a list of possible trays type with different slot numbers according to the tray type.

In the sheet "data", for each item I have a column that specifies which tray must be used for that item, and how many items I can store in each tray slot.

The target of the MACRO would be:

1) to spread/divide the different items in different tray and slot respecting the tray type and max quantity per slot.
2) to create a different sheet for each tray required.

In the excel file you can find a manual example of my expectation, I hope this is clear enough.

Thank you in advance for any assistance you can provide!

Best regards