Excel to create new rows of data in a table

[MRASHLEY]

Hello, I'm trying to achieve something in excel 365 which im not sure is possible

i have a simple table containing some products ie.

CODE	ITEM NAME	QUANTITY REQUIRED	BOX QUANTITY	NUMBER OF BOXES
0001	RED TOP	10	10	1
0002	BLUE TOP	20	10	2
0052	YELLOW TOP	20	10	2
0063	RED SHORTS	15	15	1
0064	BLUE SHORTS	30	15	2
0048	YELLOW SHORTS	45	15	3

What i want to do is create a new line in the table for each box that is needed for the order ie

CODE	ITEM NAME	QUANTITY REQUIRED	BOX QUANTITY	NUMBER OF BOXES	CARTON NUMBER
0001	RED TOP	10	10	1	1
0002	BLUE TOP	20	10	2	2
0002	BLUE TOP	20	10	2	3
0052	YELLOW TOP	20	10	2	4
0052	YELLOW TOP	20	10	2	5
0063	RED SHORTS	15	15	1	6
0064	BLUE SHORTS	30	15	2	7
0064	BLUE SHORTS	30	15	2	8
0048	YELLOW SHORTS	45	15	3	9

The yellow shorts would have 3 lines (carton number 10 & 11)

is there any way this can be done

 

[mumps]

Try:

Sub InsertRows()
    Application.ScreenUpdating = False
    Dim x As Long, lRow As Long
    lRow = Cells.Find("*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
    For x = lRow To 2 Step -1
        If Range("E" & x) > 1 Then
            Rows(x).Copy
            Rows(x + 1).Resize(Range("E" & x) - 1).Insert
        End If
    Next x
    Range("F1") = "CARTON NUMBER"
    With Range("F2")
        .Value = "1"
        .AutoFill Destination:=Range("F2").Resize(Range("E" & Rows.Count).End(xlUp).Row - 1), Type:=xlFillSeries
    End With
    Columns.AutoFit
    Application.ScreenUpdating = True
End Sub