Automated Packing Tool

This project is to create a packing list tool with 3 outputs. • The tool view which shows a matrix of what product is in what box • A packing list that shows what needs to be packed where is a list • Packing labels. This takes the list above and separates the boxes and adds titles to the cells. Therefore, these labels can be cut and added to a pdf. An example of the packing can be seen in excel sheet. The process of the tool will work as follows: USER 1. FRONT SHEET: User enters ‘FILL AMOUNT’ how full they want the boxes. The boxes should not be filled with any more volume or weight then the amount entered in the cell. E.g. if there is a 30L vol box, and the ‘FILL AMOUNT’ is 80%, the maximum volume in the box should be 24L. 2. FRONT SHEET: User enters the ‘MAX BOX WEIGHT’ and ‘BOX DIMENSIONS’ which the “FILL AMOUNT’ is applied against 3. FRONT SHEET: User enters the ‘COLUMN TO ORDER LIST BY:” which defines the column that the data will be sorted by. 4. PACKING TOOL: User copies the ‘COPY FROM SAP’. A group of data that gives all the information needed to calculate the packing. 5. FRONT SHEET: User clicks on the ‘GET PACKING’ button MACRO: 1. Macro sorts the data in PACKING TOOL TAB by the column specified by the user in the FRONT SHEET ‘COLUMN TO ORDER LIST BY’. This should always be order in alphabetical order or lowest value first 2. Macro takes the first box and fills it with the products in the first row of the COPY OF SAP (example in cell Y5) (Note, the capacity of the box is the MAX WEIGHTxFILL AMOUNT or BOX VOLUMExFILL AMOUNT, whichever is hit first). 3. When the first box is full and nothing else can added from the line below, Macro moves onto box 2. 4. This process is continued until all the products are allocated to boxes 5. Macro enters the total number of boxes into the ‘BOXES REQUIRED’ on the FRONT SHEET. 6. Macro takes the packing matrix it has created in the ‘PACKING TOOL’ tab, and copies it into a column layout in the ‘PACKING LIST’ tab. The necessary attributes are shown the attached example. 7. The Macro does the same in the ‘PACKING LABELS” tab, however, for this it separates the boxes and copies the ‘TITLES’ (that are specified at the top of the tab) above each boxes data. USER: 1. User enters the ‘AMAZON BOX CODE’ on the FRONT SHEET which in turn changes the ‘AMAZON BOX NUMBER’ on the ‘PACKING LIST’ & ‘PACKING LABELS’ tab. Any questions, please let me know.