Excel VBA - Rearranging The Data Into A Report Format

[SpicyVNoodle]

Hello, I'm new to the world of VBA and I need some help for coding.

I need the following set of data to be put into a report format as below;

Data Set

Report Format

What the report should look like;

1. Count the number of Orders and put the number in column A. For example there are total 3 orders: OR-01, AA_01, and BB~02. Then column A shows 1, 2 or 3)
2. Per each of Order, put the Item name, Product number, Color, Type, Unit (always cm) then put the Size based on the number of Quantity. For example the first item has 4 Quantity for 50 Size, then put 50 50 50 50. Once it goes over 5, put the data in the next section.
3. If the quantity is more than 10, put Size x Quality in a merged cell. For example if the size is 23 and quality is 15, put it as 23 x 15 for column G and H then the next item is put in the column I
4. Count of each of the Item and Color
5. Total of the each of Item and Color

Any part of coding for each of the section would be much appreciated. Or any ideas to how to approach to code this format would be also very appreciated.

 

[SpicyVNoodle]

I was thinking that it may be easy to use loop to grab data in each roll instead of checking the different number of Quantity.

I found this code on the web that can copy additional rows for the number of Quantity.

Sub CopyData()
Dim xRow As Long
Dim VInSertNum As Variant
xRow = 1

Do While (Cells(xRow, "A") <> "")
VInSertNum = Cells(xRow, "N")
If ((VInSertNum > 1) And IsNumeric(VInSertNum)) Then
Range(Cells(xRow, "A"), Cells(xRow, "N")).Copy
Range(Cells(xRow + 1, "A"), Cells(xRow + VInSertNum - 1, "N")).Select
Selection.Insert Shift:=xlDown
xRow = xRow + VInSertNum - 1
End If
xRow = xRow + 1
Loop
End Sub

I still don't know how to populate the Size data for only 5 times from Column G to Column K then continue inputting the Size data in the next row without inputting Name, Product No, Color, Type, Unit data.

 

[SpicyVNoodle]

I was thinking that it may be easy to use loop to grab data in each roll instead of checking the different number of Quantity.

I found this code on the web that can copy additional rows for the number of Quantity.

Sub CopyData()
Dim xRow As Long
Dim VInSertNum As Variant
xRow = 1

Do While (Cells(xRow, "A") <> "")
VInSertNum = Cells(xRow, "N")
If ((VInSertNum > 1) And IsNumeric(VInSertNum)) Then
Range(Cells(xRow, "A"), Cells(xRow, "N")).Copy
Range(Cells(xRow + 1, "A"), Cells(xRow + VInSertNum - 1, "N")).Select
Selection.Insert Shift:=xlDown
xRow = xRow + VInSertNum - 1
End If
xRow = xRow + 1
Loop
End Sub

I still don't know how to populate the Size data for only 5 times from Column G to Column K then continue inputting the Size data in the next row without inputting Name, Product No, Color, Type, Unit data.