Moving Data From One Worksheet to Another Worksheet using VBA

[lannok]

Hello - I'm attempting to convert column data to row data. I currently have 16 columns and I would like to shrink this down to 6 columns. I have about 20 hours experience working with VBA and I'm stuck. Any guidance would be most appreciated.

The current headers and layout is as follows:

	a	b	c	d	e	f	g	h	i	j	k	l	m	n	o	p	q
1	Unique ID	Z1 MKT TTC 25%ile	Z1 MKT TTC 50%ile	Z1 MKT TTC 75%ile	Z2 MKT TTC 25%ile	Z2 MKT TTC 50%ile	Z2 MKT TTC 75%ile	Z3 MKT TTC 25%ile	Z3 MKT TTC 50%ile	Z3 MKT TTC 75%ile	Z4 MKT TTC 25%ile	Z4 MKT TTC 50%ile	Z4 MKT TTC 75%ile	CPR Low - $	CPR Mid - $	CPR High - $	AVG TTC
2	SW1144:UK:IIG:057032	0	0	0	0	0	0	0	0	0	0	0	0	50	75	100	78
3	SW1144:USA:IIG:054838	2	3	4	5	6	7	8	9	10	11	12	13	14	15	16	18
4	SW1213:UK:IIG:046687	0	0	0	10	11	12	0	0	0	0	0	0	20	40	50	45
No Limit to the numbe of rows

<tbody>
</tbody>

Create VBA to organize data as follows:

	a	b	c	d	e	f
1	Unique ID	Mkt TTC %ile	Mkt TTC Value	CPR: Low/Mid/High	CPR Value	2014 TTC Value
2	SW1144:UK:IIG:057032	Z1 MKT TTC 25%ile	0	Low	50	78
3	SW1144:UK:IIG:057032	Z1 MKT TTC 50%ile	0	Mid	75	78
4	SW1144:UK:IIG:057032	Z1 MKT TTC 75%ile	0	High	100	78
5	SW1144:UK:IIG:057032	Z2 MKT TTC 25%ile	0	Low	50	78
6	SW1144:UK:IIG:057032	Z2 MKT TTC 50%ile	0	Mid	75	78
7	SW1144:UK:IIG:057032	Z2 MKT TTC 75%ile	0	High	100	78
8	SW1144:UK:IIG:057032	Z3 MKT TTC 25%ile	0	Low	50	78
9	SW1144:UK:IIG:057032	Z3 MKT TTC 50%ile	0	Mid	75	78
10	SW1144:UK:IIG:057032	Z3 MKT TTC 75%ile	0	High	100	78
11	SW1144:UK:IIG:057032	Z4 MKT TTC 25%ile	0	Low	50	78
12	SW1144:UK:IIG:057032	Z4 MKT TTC 50%ile	0	Mid	75	78
13	SW1144:UK:IIG:057032	Z4 MKT TTC 75%ile	0	High	100	78
14	SW1144:UK:IIG:057032	Same as above	Sometimes 0 and sometimes >0 both are ok	Same as above	Same as above	Same as above
3	SW1144:USA:IIG:054838	Same as above	Sometimes 0 and sometimes >0 both are ok	Same as above	Same as above	Same as above
4	SW1213:UK:IIG:046687	Same as above	Sometimes 0 and sometimes >0 both are ok	Same as above	Same as above	Same as above
No Limit to number of rows.	No Limit to number of rows.	No Limit to number of rows.	No Limit to number of rows.	No Limit to number of rows.	No Limit to number of rows.	No Limit to number of rows.

<tbody>
</tbody>

Current VBA code:

Sub CreateDataWithNewSheet()
Dim CountRows As Double
Dim CountColumns As Integer
Dim data As Variant
CountRows = ActiveSheet.UsedRange.Rows.Count
CountColumns = ActiveSheet.UsedRange.Columns.Count

data = Range(Cells(1, 1), Cells(CountRows, CountColumns))

Dim newSheet As Worksheet
Set newSheet = Sheets.Add

newSheet.Cells(1, 1).Value = "Unique ID"
newSheet.Cells(1, 2).Value = "Mkt TTC %ile"
newSheet.Cells(1, 3).Value = "Mkt TTC Value"
newSheet.Cells(1, 4).Value = "CPR: Low/Mid/High"
newSheet.Cells(1, 5).Value = "CPR: Value"
Dim StartRowPosition As Double
StartRowPosition = 2

Dim row_in_mainsheet As Double
row_in_mainsheet = 2
Dim column_in_mainsheet As Integer

Do While True

column_in_mainsheet = 2
Do While True

'Unique ID
newSheet.Cells(StartRowPosition, 1).Value = data(row_in_mainsheet, 1)

'Mkt TTC %ile
newSheet.Cells(StartRowPosition, 2).Value = data(1, column_in_mainsheet)

'Mkt TTC Value
newSheet.Cells(StartRowPosition, 3).Value = data(row_in_mainsheet, column_in_mainsheet)

'CPR: Low/Mid/High
newSheet.Cells(StartRowPosition, 4).Value = data(1, column_in_mainsheet)

'Mkt TTC Value
newSheet.Cells(StartRowPosition, 5).Value = data(row_in_mainsheet, column_in_mainsheet)


StartRowPosition = StartRowPosition + 1
If column_in_mainsheet = CountColumns Then Exit Do
column_in_mainsheet = column_in_mainsheet + 1
Loop

If row_in_mainsheet = CountRows Then Exit Do
row_in_mainsheet = row_in_mainsheet + 1
Loop


End Sub

The VBA code produces the following results:

Unique ID	Mkt TTC %ile	Mkt TTC Value	CPR: Low/Mid/High	CPR: Value
SW1144:UK:IIG:057032	Z1 MKT TTC 25%ile	0	Z1 MKT TTC 25%ile	0
SW1144:UK:IIG:057032	Z1 MKT TTC 50%ile	0	Z1 MKT TTC 50%ile	0
SW1144:UK:IIG:057032	Z1 MKT TTC 75%ile	0	Z1 MKT TTC 75%ile	0
SW1144:UK:IIG:057032	Z2 MKT TTC 25%ile	0	Z2 MKT TTC 25%ile	0
SW1144:UK:IIG:057032	Z2 MKT TTC 50%ile	0	Z2 MKT TTC 50%ile	0
SW1144:UK:IIG:057032	Z2 MKT TTC 75%ile	0	Z2 MKT TTC 75%ile	0
SW1144:UK:IIG:057032	Z3 MKT TTC 25%ile	0	Z3 MKT TTC 25%ile	0
SW1144:UK:IIG:057032	Z3 MKT TTC 50%ile	0	Z3 MKT TTC 50%ile	0
SW1144:UK:IIG:057032	Z3 MKT TTC 75%ile	0	Z3 MKT TTC 75%ile	0
SW1144:UK:IIG:057032	Z4 MKT TTC 25%ile	0	Z4 MKT TTC 25%ile	0
SW1144:UK:IIG:057032	Z4 MKT TTC 50%ile	0	Z4 MKT TTC 50%ile	0
SW1144:UK:IIG:057032	Z4 MKT TTC 75%ile	0	Z4 MKT TTC 75%ile	0
SW1144:UK:IIG:057032	CPR Low - $	50	CPR Low - $	50
SW1144:UK:IIG:057032	CPR Mid - $	75	CPR Mid - $	75
SW1144:UK:IIG:057032	CPR High - $	100	CPR High - $	100

<tbody>
</tbody>

Thanks in advance for any suggestions.

Thanks,
Ken