Repeat rows / split value

[shippey121]

Good Morning All

i have a data set that shows total copies that i need to split into Box sizes to create labels each box can hold 60 copies
this data can be inserted into this list or a complete new blank sheet,

ive seen lots of code that can repeat rows x times which i could use then manually change the values but i have over 1000 rows of data that will end up be 1000s after being split into boxes

so the table below
row 2 would be repeated 3 times 2 x 60 , 1 x 20
row 3 would remain as 1
row 4 would remain as 1
row 5 would be repeated 5 times 5 x 60

the data set is A to F so an additonal column could be added to show the box quantity

anyone any ideas would be much appreciated...thanks Craig

FINAL Quantity	Cover	Inners
140	Maths Year 1	10mm Square
60	Maths Year 2	10mm Square
5	Maths Year 3	10mm Square
300	Maths Year 4	10mm Square

 

[bebo021999]

Option Explicit
Sub split()
Dim lr&, i&, k&, remain&, qty&, rng, arr(1 To 1000000, 1 To 3)
lr = Cells(Rows.Count, "A").End(xlUp).Row
rng = Range("A2:C" & lr).Value
    For i = 1 To lr - 1
        If rng(i, 1) <= 60 Then
            k = k + 1
            arr(k, 1) = rng(i, 1): arr(k, 2) = rng(i, 2): arr(k, 3) = rng(i, 3)
        Else
            remain = rng(i, 1)
            Do While remain > 0
                k = k + 1
                qty = WorksheetFunction.Min(remain, 60)
                arr(k, 1) = qty: arr(k, 2) = rng(i, 2): arr(k, 3) = rng(i, 3)
                remain = remain - qty
            Loop
        End If
    Next
Range("A2").Resize(k, 3).Value = arr
End Sub

 

[shippey121]

well that my friend is genius..thank you..this has just saved me hours
i modifyied it slightly as may range of data is A to F but worked that out
i was thinking of running code to duplicate all lines first and then after that tackle the amounts

cheers Craig