Macro For Transpose Paste Into New Rows (Modified Example) to modify a VBA code.

[Biggy]

Hello,

Thank You all for Your huge help solving lot of issues and helping out.

My first example with this issue was in the post here. I got two response for fixing this issue. I thought about how to change code into 1st of the answers (given by Rick) to make it work for situation stated below, but couldn't - it moved data around, didn't copied some of the information, etc. Ok, I could get it work then it shows newly added item# and also more sizes, but data it copied after, e.g. name and color wasn't in the right places and some other issues. Maybe someone could give me an explanation how to modify it and what means what in the code so in the future I can modify it by myself depending on situation? Below are new situation:

Before

	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P
1	Item#	Name	Color			8	10	12	14	16	18	20	22	24	26	Price
2	TSH100	T-Shirt	White						N							$10,
3	TSH101	T-Shirt	Black													$14,
4
5	Item#	Name	Color							M	L	XL	XXL	3XL	4XL	Price
6	PLSH100	Polo Shirt	Yellow							N						$12,
7	PLSH101	Polo Shirt	Orange									N				$12,
8	PLSH102	Polo Shirt	Black													$15,

<colgroup><col style="width: 25pxpx"><col><col><col><col><col><col><col><col><col><col><col><col><col><col><col><col></colgroup><thead>
</thead><tbody>
</tbody>

Sheet3​

After

	A	B	C	D	E	F
11	Item#	Name	Color	Size	Available	Price
12	TSH100	T-Shirt	White	8		$10,
13	TSH100	T-Shirt	White	10		$10,
14	TSH100	T-Shirt	White	12		$10,
15	TSH100	T-Shirt	White	14	N	$10,
16	TSH100	T-Shirt	White	16		$10,
17	TSH100	T-Shirt	White	18		$10,
18	TSH100	T-Shirt	White	20		$10,
19	TSH100	T-Shirt	White	22		$10,
20	TSH100	T-Shirt	White	24		$10,
21	TSH100	T-Shirt	White	26		$10,
22	TSH101	T-Shirt	Black	8		$14,
23	TSH101	T-Shirt	Black	10		$14,
24	TSH101	T-Shirt	Black	12		$14,
25	TSH101	T-Shirt	Black	14		$14,
26	TSH101	T-Shirt	Black	16		$14,
27	TSH101	T-Shirt	Black	18		$14,
28	TSH101	T-Shirt	Black	20		$14,
29	TSH101	T-Shirt	Black	22		$14,
30	TSH101	T-Shirt	Black	24		$14,
31	TSH101	T-Shirt	Black	26		$14,
32
33
34	Item#	Name	Color	Size	Available	Price
35	PLSH100	Polo Shirt	Yellow	M	N	$14,
36	PLSH100	Polo Shirt	Yellow	L		$14,
37	PLSH100	Polo Shirt	Yellow	XL		$14,
38	PLSH100	Polo Shirt	Yellow	XXL		$14,
39	PLSH100	Polo Shirt	Yellow	3XL		$14,
40	PLSH100	Polo Shirt	Yellow	4XL		$14,
41	PLSH101	Polo Shirt	Orange	M		$14,
42	PLSH101	Polo Shirt	Orange	L		$14,
43	PLSH101	Polo Shirt	Orange	XL	N	$14,
44	PLSH101	Polo Shirt	Orange	XXL		$14,
45	PLSH101	Polo Shirt	Orange	3XL		$14,
46	PLSH101	Polo Shirt	Orange	4XL		$14,
47	PLSH102	Polo Shirt	Black	M		$15,
48	PLSH102	Polo Shirt	Black	L		$15,
49	PLSH102	Polo Shirt	Black	XL		$15,
50	PLSH102	Polo Shirt	Black	XXL		$15,
51	PLSH102	Polo Shirt	Black	3XL		$15,
52	PLSH102	Polo Shirt	Black	4XL		$15,

<colgroup><col style="width: 25pxpx"><col><col><col><col><col><col></colgroup><thead>
</thead><tbody>
</tbody>

Sheet3​

Here are code which was made by a Rick for situation then There wasn't Item # , so in Column A was a "name", but sizes went till Column L. Hopefully someone will be able to help me with this one.

Sub TransposeSizeTable()
  Dim X As Long, Cnt As Long, LastRow As Long, NewStart As Long, Ar As Range
  LastRow = Cells(Rows.Count, "A").End(xlUp).Row
  NewStart = LastRow + 5
  For Each Ar In Range("A1:A" & LastRow).SpecialCells(xlConstants).Areas
    Cells(NewStart, "A").Resize(, 5) = Array("Name", "Color", "Size", "Available", "Price")
    Cnt = Application.CountA(Ar(1).Offset(, 2).Resize(, 10))
    For X = 1 To Ar.Rows.Count - 1
      Cells(NewStart + X, "A").Resize(Cnt, 2).Value = Ar(1).Offset(X).Resize(, 2).Value
      Cells(NewStart + X, "C").Resize(Cnt) = Application.Transpose(Ar(1).Offset(, 12 - Cnt).Resize(, Cnt))
      Cells(NewStart + X, "E").Resize(Cnt).Value = Ar(1).Offset(X, 12).Value
      If X = 1 Then
        Cells(NewStart + X, "D").Resize(Cnt) = Application.Transpose(Ar(1).Offset(X, 12 - Cnt).Resize(, Cnt))
      Else
        Cells(NewStart + X, "D").Resize(Cnt) = Application.Transpose(Ar(1).Offset(X, 12 - Cnt).Resize(, Cnt))
      End If
      NewStart = NewStart + Cnt - 1
    Next
    NewStart = Cells(Rows.Count, "A").End(xlUp).Row + 3
  Next
End Sub

Sorry for bothering You all, but I really want to understand how it works. So Thank You for reading this!

Sincerely,
Biggy