Moving Data From One Worksheet to Another Worksheet using VBA

L

lannok

New Member
Joined
Sep 16, 2014
Messages
1
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:

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


Create VBA to organize data as follows:

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


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:

[TABLE="width: 725"]
<tbody>[TR]
[TD]Unique ID[/TD]
[TD]Mkt TTC %ile[/TD]
[TD]Mkt TTC Value[/TD]
[TD]CPR: Low/Mid/High[/TD]
[TD]CPR: Value[/TD]
[/TR]
[TR]
[TD]SW1144:UK:IIG:057032[/TD]
[TD]Z1 MKT TTC 25%ile[/TD]
[TD="align: right"]0[/TD]
[TD]Z1 MKT TTC 25%ile[/TD]
[TD="align: right"]0[/TD]
[/TR]
[TR]
[TD]SW1144:UK:IIG:057032[/TD]
[TD]Z1 MKT TTC 50%ile[/TD]
[TD="align: right"]0[/TD]
[TD]Z1 MKT TTC 50%ile[/TD]
[TD="align: right"]0[/TD]
[/TR]
[TR]
[TD]SW1144:UK:IIG:057032[/TD]
[TD]Z1 MKT TTC 75%ile[/TD]
[TD="align: right"]0[/TD]
[TD]Z1 MKT TTC 75%ile[/TD]
[TD="align: right"]0[/TD]
[/TR]
[TR]
[TD]SW1144:UK:IIG:057032[/TD]
[TD]Z2 MKT TTC 25%ile[/TD]
[TD="align: right"]0[/TD]
[TD]Z2 MKT TTC 25%ile[/TD]
[TD="align: right"]0[/TD]
[/TR]
[TR]
[TD]SW1144:UK:IIG:057032[/TD]
[TD]Z2 MKT TTC 50%ile[/TD]
[TD="align: right"]0[/TD]
[TD]Z2 MKT TTC 50%ile[/TD]
[TD="align: right"]0[/TD]
[/TR]
[TR]
[TD]SW1144:UK:IIG:057032[/TD]
[TD]Z2 MKT TTC 75%ile[/TD]
[TD="align: right"]0[/TD]
[TD]Z2 MKT TTC 75%ile[/TD]
[TD="align: right"]0[/TD]
[/TR]
[TR]
[TD]SW1144:UK:IIG:057032[/TD]
[TD]Z3 MKT TTC 25%ile[/TD]
[TD="align: right"]0[/TD]
[TD]Z3 MKT TTC 25%ile[/TD]
[TD="align: right"]0[/TD]
[/TR]
[TR]
[TD]SW1144:UK:IIG:057032[/TD]
[TD]Z3 MKT TTC 50%ile[/TD]
[TD="align: right"]0[/TD]
[TD]Z3 MKT TTC 50%ile[/TD]
[TD="align: right"]0[/TD]
[/TR]
[TR]
[TD]SW1144:UK:IIG:057032[/TD]
[TD]Z3 MKT TTC 75%ile[/TD]
[TD="align: right"]0[/TD]
[TD]Z3 MKT TTC 75%ile[/TD]
[TD="align: right"]0[/TD]
[/TR]
[TR]
[TD]SW1144:UK:IIG:057032[/TD]
[TD]Z4 MKT TTC 25%ile[/TD]
[TD="align: right"]0[/TD]
[TD]Z4 MKT TTC 25%ile[/TD]
[TD="align: right"]0[/TD]
[/TR]
[TR]
[TD]SW1144:UK:IIG:057032[/TD]
[TD]Z4 MKT TTC 50%ile[/TD]
[TD="align: right"]0[/TD]
[TD]Z4 MKT TTC 50%ile[/TD]
[TD="align: right"]0[/TD]
[/TR]
[TR]
[TD]SW1144:UK:IIG:057032[/TD]
[TD]Z4 MKT TTC 75%ile[/TD]
[TD="align: right"]0[/TD]
[TD]Z4 MKT TTC 75%ile[/TD]
[TD="align: right"]0[/TD]
[/TR]
[TR]
[TD]SW1144:UK:IIG:057032[/TD]
[TD]CPR Low - $[/TD]
[TD="align: right"]50[/TD]
[TD]CPR Low - $[/TD]
[TD="align: right"]50[/TD]
[/TR]
[TR]
[TD]SW1144:UK:IIG:057032[/TD]
[TD]CPR Mid - $[/TD]
[TD="align: right"]75[/TD]
[TD]CPR Mid - $[/TD]
[TD="align: right"]75[/TD]
[/TR]
[TR]
[TD]SW1144:UK:IIG:057032[/TD]
[TD]CPR High - $[/TD]
[TD="align: right"]100[/TD]
[TD]CPR High - $[/TD]
[TD="align: right"]100[/TD]
[/TR]
</tbody>[/TABLE]

Thanks in advance for any suggestions.

Thanks,
Ken
 
You must log in or register to reply here.

Similar threads

S
  • Question Question
Create New Sheet with Specific Name Based on Variable Cell
Replies
0
Views
295
SBExcel123
S
J
  • Solved
VBA Code To Loop Through One Worksheet and Paste Certain Values Into Another
Replies
5
Views
232
Peter_SSs
Peter_SSs
BigPaws
  • Question Question
VBA to create new worksheets from a template, populate the new worksheet by copying data from another sheet to specific locations.
Replies
0
Views
238
BigPaws
BigPaws
M
  • Solved
VBA Transpose data from one sheet into another
Replies
9
Views
665
madi1004
M
Z
  • Question Question
VBA code to create a new workbook from multiple sheets of current workbook
Replies
9
Views
5K
Micron
M

Forum statistics

Threads
1,226,871
Messages
6,193,443
Members
453,799
Latest member
shanley ducker

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