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

B

Biggy

New Member
Joined
Feb 14, 2014
Messages
22
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
ABCDEFGHIJKLMNOP
TSH100T-Shirt
TSH101T-Shirt
PLSH100Polo ShirtN
PLSH101Polo ShirtN
PLSH102Polo Shirt

<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>
[TD="align: center"]1[/TD]
[TD="bgcolor: #D9D9D9"]Item#[/TD]
[TD="bgcolor: #D9D9D9"]Name[/TD]
[TD="bgcolor: #D9D9D9, align: center"]Color[/TD]
[TD="bgcolor: #D9D9D9, align: center"][/TD]
[TD="bgcolor: #D9D9D9, align: center"][/TD]
[TD="bgcolor: #D9D9D9, align: center"]8[/TD]
[TD="bgcolor: #D9D9D9, align: center"]10[/TD]
[TD="bgcolor: #D9D9D9, align: center"]12[/TD]
[TD="bgcolor: #D9D9D9, align: center"]14[/TD]
[TD="bgcolor: #D9D9D9, align: center"]16[/TD]
[TD="bgcolor: #D9D9D9, align: center"]18[/TD]
[TD="bgcolor: #D9D9D9, align: center"]20[/TD]
[TD="bgcolor: #D9D9D9, align: center"]22[/TD]
[TD="bgcolor: #D9D9D9, align: center"]24[/TD]
[TD="bgcolor: #D9D9D9, align: center"]26[/TD]
[TD="bgcolor: #D9D9D9, align: center"]Price[/TD]

[TD="align: center"]2[/TD]

[TD="align: center"]White[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"]N[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"]$10,[/TD]

[TD="align: center"]3[/TD]

[TD="align: center"]Black[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"]$14,[/TD]

[TD="align: center"]4[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: center"][/TD]

[TD="align: center"]5[/TD]
[TD="bgcolor: #D9D9D9"]Item#[/TD]
[TD="bgcolor: #D9D9D9"]Name[/TD]
[TD="bgcolor: #D9D9D9, align: center"]Color[/TD]
[TD="bgcolor: #D9D9D9, align: right"][/TD]
[TD="bgcolor: #D9D9D9, align: right"][/TD]
[TD="bgcolor: #D9D9D9, align: right"][/TD]
[TD="bgcolor: #D9D9D9, align: right"][/TD]
[TD="bgcolor: #D9D9D9, align: center"][/TD]
[TD="bgcolor: #D9D9D9, align: center"][/TD]
[TD="bgcolor: #D9D9D9, align: center"]M[/TD]
[TD="bgcolor: #D9D9D9, align: center"]L[/TD]
[TD="bgcolor: #D9D9D9, align: center"]XL[/TD]
[TD="bgcolor: #D9D9D9, align: center"]XXL[/TD]
[TD="bgcolor: #D9D9D9, align: center"]3XL[/TD]
[TD="bgcolor: #D9D9D9, align: center"]4XL[/TD]
[TD="bgcolor: #D9D9D9, align: center"]Price[/TD]

[TD="align: center"]6[/TD]

[TD="align: center"]Yellow[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: center"]$12,[/TD]

[TD="align: center"]7[/TD]

[TD="align: center"]Orange[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: center"]$12,[/TD]

[TD="align: center"]8[/TD]

[TD="align: center"]Black[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: center"]$15,[/TD]

</tbody>
Sheet3​



After
ABCDEF
TSH100
TSH100
TSH100
TSH100
TSH100
TSH100
TSH100
TSH100
TSH100
TSH100
TSH101
TSH101
TSH101
TSH101
TSH101
TSH101
TSH101
TSH101
TSH101
TSH101
PLSH100
PLSH100
PLSH100
PLSH100
PLSH100
PLSH100
PLSH101
PLSH101
PLSH101
PLSH101
PLSH101
PLSH101
PLSH102
PLSH102
PLSH102
PLSH102
PLSH102
PLSH102

<colgroup><col style="width: 25pxpx"><col><col><col><col><col><col></colgroup><thead>
</thead><tbody>
[TD="align: center"]11[/TD]
[TD="bgcolor: #BFBFBF, align: center"]Item#[/TD]
[TD="bgcolor: #BFBFBF, align: center"]Name[/TD]
[TD="bgcolor: #BFBFBF, align: center"]Color[/TD]
[TD="bgcolor: #BFBFBF, align: center"]Size[/TD]
[TD="bgcolor: #BFBFBF, align: center"]Available[/TD]
[TD="bgcolor: #BFBFBF, align: center"]Price[/TD]

[TD="align: center"]12[/TD]

[TD="bgcolor: #FFFFFF"]T-Shirt[/TD]
[TD="bgcolor: #FFFFFF, align: center"]White[/TD]
[TD="bgcolor: #FFFFFF, align: center"]8[/TD]
[TD="bgcolor: #FFFFFF, align: center"][/TD]
[TD="bgcolor: #FFFFFF, align: center"]$10,[/TD]

[TD="align: center"]13[/TD]

[TD="bgcolor: #FFFFFF"]T-Shirt[/TD]
[TD="bgcolor: #FFFFFF, align: center"]White[/TD]
[TD="bgcolor: #FFFFFF, align: center"]10[/TD]
[TD="bgcolor: #FFFFFF, align: center"][/TD]
[TD="bgcolor: #FFFFFF, align: center"]$10,[/TD]

[TD="align: center"]14[/TD]

[TD="bgcolor: #FFFFFF"]T-Shirt[/TD]
[TD="bgcolor: #FFFFFF, align: center"]White[/TD]
[TD="bgcolor: #FFFFFF, align: center"]12[/TD]
[TD="bgcolor: #FFFFFF, align: center"][/TD]
[TD="bgcolor: #FFFFFF, align: center"]$10,[/TD]

[TD="align: center"]15[/TD]

[TD="bgcolor: #FFFFFF"]T-Shirt[/TD]
[TD="bgcolor: #FFFFFF, align: center"]White[/TD]
[TD="bgcolor: #FFFFFF, align: center"]14[/TD]
[TD="bgcolor: #FFFFFF, align: center"]N[/TD]
[TD="bgcolor: #FFFFFF, align: center"]$10,[/TD]

[TD="align: center"]16[/TD]

[TD="bgcolor: #FFFFFF"]T-Shirt[/TD]
[TD="bgcolor: #FFFFFF, align: center"]White[/TD]
[TD="bgcolor: #FFFFFF, align: center"]16[/TD]
[TD="bgcolor: #FFFFFF, align: center"][/TD]
[TD="bgcolor: #FFFFFF, align: center"]$10,[/TD]

[TD="align: center"]17[/TD]

[TD="bgcolor: #FFFFFF"]T-Shirt[/TD]
[TD="bgcolor: #FFFFFF, align: center"]White[/TD]
[TD="bgcolor: #FFFFFF, align: center"]18[/TD]
[TD="bgcolor: #FFFFFF, align: center"][/TD]
[TD="bgcolor: #FFFFFF, align: center"]$10,[/TD]

[TD="align: center"]18[/TD]

[TD="bgcolor: #FFFFFF"]T-Shirt[/TD]
[TD="bgcolor: #FFFFFF, align: center"]White[/TD]
[TD="bgcolor: #FFFFFF, align: center"]20[/TD]
[TD="bgcolor: #FFFFFF, align: center"][/TD]
[TD="bgcolor: #FFFFFF, align: center"]$10,[/TD]

[TD="align: center"]19[/TD]

[TD="bgcolor: #FFFFFF"]T-Shirt[/TD]
[TD="bgcolor: #FFFFFF, align: center"]White[/TD]
[TD="bgcolor: #FFFFFF, align: center"]22[/TD]
[TD="bgcolor: #FFFFFF, align: center"][/TD]
[TD="bgcolor: #FFFFFF, align: center"]$10,[/TD]

[TD="align: center"]20[/TD]

[TD="bgcolor: #FFFFFF"]T-Shirt[/TD]
[TD="bgcolor: #FFFFFF, align: center"]White[/TD]
[TD="bgcolor: #FFFFFF, align: center"]24[/TD]
[TD="bgcolor: #FFFFFF, align: center"][/TD]
[TD="bgcolor: #FFFFFF, align: center"]$10,[/TD]

[TD="align: center"]21[/TD]

[TD="bgcolor: #FFFFFF"]T-Shirt[/TD]
[TD="bgcolor: #FFFFFF, align: center"]White[/TD]
[TD="bgcolor: #FFFFFF, align: center"]26[/TD]
[TD="bgcolor: #FFFFFF, align: center"][/TD]
[TD="bgcolor: #FFFFFF, align: center"]$10,[/TD]

[TD="align: center"]22[/TD]

[TD="bgcolor: #FFFFFF"]T-Shirt[/TD]
[TD="bgcolor: #FFFFFF, align: center"]Black[/TD]
[TD="bgcolor: #FFFFFF, align: center"]8[/TD]
[TD="bgcolor: #FFFFFF, align: center"][/TD]
[TD="bgcolor: #FFFFFF, align: center"]$14,[/TD]

[TD="align: center"]23[/TD]

[TD="bgcolor: #FFFFFF"]T-Shirt[/TD]
[TD="bgcolor: #FFFFFF, align: center"]Black[/TD]
[TD="bgcolor: #FFFFFF, align: center"]10[/TD]
[TD="bgcolor: #FFFFFF, align: center"][/TD]
[TD="bgcolor: #FFFFFF, align: center"]$14,[/TD]

[TD="align: center"]24[/TD]

[TD="bgcolor: #FFFFFF"]T-Shirt[/TD]
[TD="bgcolor: #FFFFFF, align: center"]Black[/TD]
[TD="bgcolor: #FFFFFF, align: center"]12[/TD]
[TD="bgcolor: #FFFFFF, align: center"][/TD]
[TD="bgcolor: #FFFFFF, align: center"]$14,[/TD]

[TD="align: center"]25[/TD]

[TD="bgcolor: #FFFFFF"]T-Shirt[/TD]
[TD="bgcolor: #FFFFFF, align: center"]Black[/TD]
[TD="bgcolor: #FFFFFF, align: center"]14[/TD]
[TD="bgcolor: #FFFFFF, align: center"][/TD]
[TD="bgcolor: #FFFFFF, align: center"]$14,[/TD]

[TD="align: center"]26[/TD]

[TD="bgcolor: #FFFFFF"]T-Shirt[/TD]
[TD="bgcolor: #FFFFFF, align: center"]Black[/TD]
[TD="bgcolor: #FFFFFF, align: center"]16[/TD]
[TD="bgcolor: #FFFFFF, align: center"][/TD]
[TD="bgcolor: #FFFFFF, align: center"]$14,[/TD]

[TD="align: center"]27[/TD]

[TD="bgcolor: #FFFFFF"]T-Shirt[/TD]
[TD="bgcolor: #FFFFFF, align: center"]Black[/TD]
[TD="bgcolor: #FFFFFF, align: center"]18[/TD]
[TD="bgcolor: #FFFFFF, align: center"][/TD]
[TD="bgcolor: #FFFFFF, align: center"]$14,[/TD]

[TD="align: center"]28[/TD]

[TD="bgcolor: #FFFFFF"]T-Shirt[/TD]
[TD="bgcolor: #FFFFFF, align: center"]Black[/TD]
[TD="bgcolor: #FFFFFF, align: center"]20[/TD]
[TD="bgcolor: #FFFFFF, align: center"][/TD]
[TD="bgcolor: #FFFFFF, align: center"]$14,[/TD]

[TD="align: center"]29[/TD]

[TD="bgcolor: #FFFFFF"]T-Shirt[/TD]
[TD="bgcolor: #FFFFFF, align: center"]Black[/TD]
[TD="bgcolor: #FFFFFF, align: center"]22[/TD]
[TD="bgcolor: #FFFFFF, align: center"][/TD]
[TD="bgcolor: #FFFFFF, align: center"]$14,[/TD]

[TD="align: center"]30[/TD]

[TD="bgcolor: #FFFFFF"]T-Shirt[/TD]
[TD="bgcolor: #FFFFFF, align: center"]Black[/TD]
[TD="bgcolor: #FFFFFF, align: center"]24[/TD]
[TD="bgcolor: #FFFFFF, align: center"][/TD]
[TD="bgcolor: #FFFFFF, align: center"]$14,[/TD]

[TD="align: center"]31[/TD]

[TD="bgcolor: #FFFFFF"]T-Shirt[/TD]
[TD="bgcolor: #FFFFFF, align: center"]Black[/TD]
[TD="bgcolor: #FFFFFF, align: center"]26[/TD]
[TD="bgcolor: #FFFFFF, align: center"][/TD]
[TD="bgcolor: #FFFFFF, align: center"]$14,[/TD]

[TD="align: center"]32[/TD]
[TD="align: center"][/TD]
[TD="bgcolor: #FFFFFF, align: right"][/TD]
[TD="bgcolor: #FFFFFF, align: center"][/TD]
[TD="bgcolor: #FFFFFF, align: center"][/TD]
[TD="bgcolor: #FFFFFF, align: center"][/TD]
[TD="bgcolor: #FFFFFF, align: center"][/TD]

[TD="align: center"]33[/TD]
[TD="align: center"][/TD]
[TD="bgcolor: #FFFFFF, align: right"][/TD]
[TD="bgcolor: #FFFFFF, align: center"][/TD]
[TD="bgcolor: #FFFFFF, align: center"][/TD]
[TD="bgcolor: #FFFFFF, align: center"][/TD]
[TD="bgcolor: #FFFFFF, align: center"][/TD]

[TD="align: center"]34[/TD]
[TD="bgcolor: #BFBFBF, align: center"]Item#[/TD]
[TD="bgcolor: #BFBFBF, align: center"]Name[/TD]
[TD="bgcolor: #BFBFBF, align: center"]Color[/TD]
[TD="bgcolor: #BFBFBF, align: center"]Size[/TD]
[TD="bgcolor: #BFBFBF, align: center"]Available[/TD]
[TD="bgcolor: #BFBFBF, align: center"]Price[/TD]

[TD="align: center"]35[/TD]

[TD="bgcolor: #FFFFFF"]Polo Shirt[/TD]
[TD="bgcolor: #FFFFFF, align: center"]Yellow[/TD]
[TD="bgcolor: #FFFFFF, align: center"]M[/TD]
[TD="bgcolor: #FFFFFF, align: center"]N[/TD]
[TD="bgcolor: #FFFFFF, align: center"]$14,[/TD]

[TD="align: center"]36[/TD]

[TD="bgcolor: #FFFFFF"]Polo Shirt[/TD]
[TD="bgcolor: #FFFFFF, align: center"]Yellow[/TD]
[TD="bgcolor: #FFFFFF, align: center"]L[/TD]
[TD="bgcolor: #FFFFFF, align: center"][/TD]
[TD="bgcolor: #FFFFFF, align: center"]$14,[/TD]

[TD="align: center"]37[/TD]

[TD="bgcolor: #FFFFFF"]Polo Shirt[/TD]
[TD="bgcolor: #FFFFFF, align: center"]Yellow[/TD]
[TD="bgcolor: #FFFFFF, align: center"]XL[/TD]
[TD="bgcolor: #FFFFFF, align: center"][/TD]
[TD="bgcolor: #FFFFFF, align: center"]$14,[/TD]

[TD="align: center"]38[/TD]

[TD="bgcolor: #FFFFFF"]Polo Shirt[/TD]
[TD="bgcolor: #FFFFFF, align: center"]Yellow[/TD]
[TD="bgcolor: #FFFFFF, align: center"]XXL[/TD]
[TD="bgcolor: #FFFFFF, align: center"][/TD]
[TD="bgcolor: #FFFFFF, align: center"]$14,[/TD]

[TD="align: center"]39[/TD]

[TD="bgcolor: #FFFFFF"]Polo Shirt[/TD]
[TD="bgcolor: #FFFFFF, align: center"]Yellow[/TD]
[TD="bgcolor: #FFFFFF, align: center"]3XL[/TD]
[TD="bgcolor: #FFFFFF, align: center"][/TD]
[TD="bgcolor: #FFFFFF, align: center"]$14,[/TD]

[TD="align: center"]40[/TD]

[TD="bgcolor: #FFFFFF"]Polo Shirt[/TD]
[TD="bgcolor: #FFFFFF, align: center"]Yellow[/TD]
[TD="bgcolor: #FFFFFF, align: center"]4XL[/TD]
[TD="bgcolor: #FFFFFF, align: center"][/TD]
[TD="bgcolor: #FFFFFF, align: center"]$14,[/TD]

[TD="align: center"]41[/TD]

[TD="bgcolor: #FFFFFF"]Polo Shirt[/TD]
[TD="bgcolor: #FFFFFF, align: center"]Orange[/TD]
[TD="bgcolor: #FFFFFF, align: center"]M[/TD]
[TD="bgcolor: #FFFFFF, align: center"][/TD]
[TD="bgcolor: #FFFFFF, align: center"]$14,[/TD]

[TD="align: center"]42[/TD]

[TD="bgcolor: #FFFFFF"]Polo Shirt[/TD]
[TD="bgcolor: #FFFFFF, align: center"]Orange[/TD]
[TD="bgcolor: #FFFFFF, align: center"]L[/TD]
[TD="bgcolor: #FFFFFF, align: center"][/TD]
[TD="bgcolor: #FFFFFF, align: center"]$14,[/TD]

[TD="align: center"]43[/TD]

[TD="bgcolor: #FFFFFF"]Polo Shirt[/TD]
[TD="bgcolor: #FFFFFF, align: center"]Orange[/TD]
[TD="bgcolor: #FFFFFF, align: center"]XL[/TD]
[TD="bgcolor: #FFFFFF, align: center"]N[/TD]
[TD="bgcolor: #FFFFFF, align: center"]$14,[/TD]

[TD="align: center"]44[/TD]

[TD="bgcolor: #FFFFFF"]Polo Shirt[/TD]
[TD="bgcolor: #FFFFFF, align: center"]Orange[/TD]
[TD="bgcolor: #FFFFFF, align: center"]XXL[/TD]
[TD="bgcolor: #FFFFFF, align: center"][/TD]
[TD="bgcolor: #FFFFFF, align: center"]$14,[/TD]

[TD="align: center"]45[/TD]

[TD="bgcolor: #FFFFFF"]Polo Shirt[/TD]
[TD="bgcolor: #FFFFFF, align: center"]Orange[/TD]
[TD="bgcolor: #FFFFFF, align: center"]3XL[/TD]
[TD="bgcolor: #FFFFFF, align: center"][/TD]
[TD="bgcolor: #FFFFFF, align: center"]$14,[/TD]

[TD="align: center"]46[/TD]

[TD="bgcolor: #FFFFFF"]Polo Shirt[/TD]
[TD="bgcolor: #FFFFFF, align: center"]Orange[/TD]
[TD="bgcolor: #FFFFFF, align: center"]4XL[/TD]
[TD="bgcolor: #FFFFFF, align: center"][/TD]
[TD="bgcolor: #FFFFFF, align: center"]$14,[/TD]

[TD="align: center"]47[/TD]

[TD="bgcolor: #FFFFFF"]Polo Shirt[/TD]
[TD="bgcolor: #FFFFFF, align: center"]Black[/TD]
[TD="bgcolor: #FFFFFF, align: center"]M[/TD]
[TD="bgcolor: #FFFFFF, align: center"][/TD]
[TD="bgcolor: #FFFFFF, align: center"]$15,[/TD]

[TD="align: center"]48[/TD]

[TD="bgcolor: #FFFFFF"]Polo Shirt[/TD]
[TD="bgcolor: #FFFFFF, align: center"]Black[/TD]
[TD="bgcolor: #FFFFFF, align: center"]L[/TD]
[TD="bgcolor: #FFFFFF, align: center"][/TD]
[TD="bgcolor: #FFFFFF, align: center"]$15,[/TD]

[TD="align: center"]49[/TD]

[TD="bgcolor: #FFFFFF"]Polo Shirt[/TD]
[TD="bgcolor: #FFFFFF, align: center"]Black[/TD]
[TD="bgcolor: #FFFFFF, align: center"]XL[/TD]
[TD="bgcolor: #FFFFFF, align: center"][/TD]
[TD="bgcolor: #FFFFFF, align: center"]$15,[/TD]

[TD="align: center"]50[/TD]

[TD="bgcolor: #FFFFFF"]Polo Shirt[/TD]
[TD="bgcolor: #FFFFFF, align: center"]Black[/TD]
[TD="bgcolor: #FFFFFF, align: center"]XXL[/TD]
[TD="bgcolor: #FFFFFF, align: center"][/TD]
[TD="bgcolor: #FFFFFF, align: center"]$15,[/TD]

[TD="align: center"]51[/TD]

[TD="bgcolor: #FFFFFF"]Polo Shirt[/TD]
[TD="bgcolor: #FFFFFF, align: center"]Black[/TD]
[TD="bgcolor: #FFFFFF, align: center"]3XL[/TD]
[TD="align: right"][/TD]
[TD="bgcolor: #FFFFFF, align: center"]$15,[/TD]

[TD="align: center"]52[/TD]

[TD="bgcolor: #FFFFFF"]Polo Shirt[/TD]
[TD="bgcolor: #FFFFFF, align: center"]Black[/TD]
[TD="bgcolor: #FFFFFF, align: center"]4XL[/TD]
[TD="align: right"][/TD]
[TD="bgcolor: #FFFFFF, align: center"]$15,[/TD]

</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.

Code: 
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
 

Excel Facts

Which Excel functions can ignore hidden rows?
The SUBTOTAL and AGGREGATE functions ignore hidden rows. AGGREGATE can also exclude error cells and more.
You must log in or register to reply here.

Similar threads

Z
  • Solved
Drop down list using unique but with changed number of rows
Replies
5
Views
343
Fluff
Fluff
F
  • Question Question
Custom search bar - Creating a front end for My Sold Research Tool.
Replies
0
Views
198
frewert
F
gtd526
  • Question Question
vba Sorting Columns w/ 2 Criteria's
Replies
1
Views
320
gtd526
gtd526
Q
  • Solved
VBA Macro to Find Missing and Duplicate Data
2 3
Replies
20
Views
1K
kevin9999
K
GMC The Macro Man
  • Question Question
Copy n Paste Filtered Data
Replies
1
Views
164
GMC The Macro Man
GMC The Macro Man

Forum statistics

Threads
1,223,214
Messages
6,170,771
Members
452,353
Latest member
strainu

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top