Excel VBA to create multi rows based on column header

[Stildawn]

Hi All

I have this sort of data below:

I'm running For Next to go through the data row by row, what I need to do on each row is:

- Create a new row on a separate sheet with the "Style" combined with size (red columns) into A​

- Next to the new row, put the number under that size​

- Next to that use the "unit price" times the number to get total value​

​

- Repeat the above again for the next red size column and continue until all red columns are done.​

​

For example, the first row of data above (in row 2), I would need the new sheet to look like this:

Style	QTY	Value
ABC123-S	15	$90
ABC123-M	20	$120
ABC123-L	20	$120
ABC123-XL	30	$180
ABC123-XXL	20	$120

And then the next sytle "ABC124" below that, again with all the sizes added on.

Hope this makes sense, basically I need to create style codes for each style + size, and then list the QTY and the total value, but over hundreds of lines.

Is there a easy VBA way to do this? I'm sure I could code it out, but my code would be super long and complicated haha.

Thanks in advance.

 

[bebo021999]

Try this code:

Option Explicit
Sub test()
Dim lr&, i&, j&, k&, rng, res(1 To 100000, 1 To 3)
lr = Cells(Rows.Count, "A").End(xlUp).Row
rng = Range("A1:I" & lr)
For i = 2 To UBound(rng)
    For j = 2 To 8
        If rng(i, j) > 0 Then
            k = k + 1
            res(k, 1) = rng(i, 1) & "-" & rng(1, j)
            res(k, 2) = rng(i, j)
            res(k, 3) = rng(i, j) * rng(i, 9)
        End If
    Next
Next
If k > 0 Then Range("K2").Resize(k, 3).Value = res ' adjust destination as needed
End Sub

Book1
	A	B	C	D	E	F	G	H	I	J	K	L	M
1		XS	S	M	L	XL	XXL	XXXL	U.P
2	ABC123		15	20	18	30	35	40	6		ABC123-S	15	90
3	ABC124		16	21	19	31	36	41	7		ABC123-M	20	120
4	ABC125		17	22	20	32	37	42	8		ABC123-L	18	108
5	ABC126		18	23	21	33	38	43	9		ABC123-XL	30	180
6	ABC127	6	19	24	22	34	39	44	10		ABC123-XXL	35	210
7	ABC128		20	25	23	35	40	45	11		ABC123-XXXL	40	240
8	ABC129		21	26	24	36	41	46	12		ABC124-S	16	112
9	ABC130	3	22	27	25	37	42	47	13		ABC124-M	21	147
10	ABC131		23	28	26	38	43	48	14		ABC124-L	19	133
11	ABC132		24	29	27	39	44	49	15		ABC124-XL	31	217
12	ABC133		25	30	28	40	45	50	16		ABC124-XXL	36	252
13											ABC124-XXXL	41	287
14											ABC125-S	17	136
15											ABC125-M	22	176
16											ABC125-L	20	160
17											ABC125-XL	32	256
18											ABC125-XXL	37	296
19											ABC125-XXXL	42	336
20											ABC126-S	18	162
21											ABC126-M	23	207
22											ABC126-L	21	189
23											ABC126-XL	33	297
24											ABC126-XXL	38	342
25											ABC126-XXXL	43	387
26											ABC127-XS	6	60
27											ABC127-S	19	190
28											ABC127-M	24	240
29											ABC127-L	22	220
30											ABC127-XL	34	340
31											ABC127-XXL	39	390
32											ABC127-XXXL	44	440
33											ABC128-S	20	220
34											ABC128-M	25	275
35											ABC128-L	23	253
36											ABC128-XL	35	385
37											ABC128-XXL	40	440
38											ABC128-XXXL	45	495
39											ABC129-S	21	252
40											ABC129-M	26	312
41											ABC129-L	24	288
42											ABC129-XL	36	432
43											ABC129-XXL	41	492
44											ABC129-XXXL	46	552
45											ABC130-XS	3	39
46											ABC130-S	22	286
47											ABC130-M	27	351
48											ABC130-L	25	325
49											ABC130-XL	37	481
50											ABC130-XXL	42	546
51											ABC130-XXXL	47	611
52											ABC131-S	23	322
53											ABC131-M	28	392
54											ABC131-L	26	364
55											ABC131-XL	38	532
56											ABC131-XXL	43	602
57											ABC131-XXXL	48	672
58											ABC132-S	24	360
59											ABC132-M	29	435
60											ABC132-L	27	405
61											ABC132-XL	39	585
62											ABC132-XXL	44	660
63											ABC132-XXXL	49	735
64											ABC133-S	25	400
65											ABC133-M	30	480
66											ABC133-L	28	448
67											ABC133-XL	40	640
68											ABC133-XXL	45	720
69											ABC133-XXXL	50	800
Sheet1