Help Please with Transpose to new sheet

gibbonsm

New Member
Joined
Nov 24, 2013
Messages
12
Good Morning,
I am trying to copy data that looks like the following
Is there a problem
Name
Account number
Comments
Overall rating
Date
yes
Jim
1234
Need to do this
7
20140312
no
Jill
5678
This needs to be fixed
6
20140414
yes
Bob
4321
Excellent works great
1
20140313
no
Joe
3214
Excellent works great
2
20140215
yes
Mike
4123
Need to do this
7
20140617
no
Dan
4213
This needs to be fixed
6
20140712
yes
Janet
1111
Excellent works great
1
20140818
no
Jumior
1112
Excellent works great
2
20140919

<tbody>
</tbody>
















I want to make the data look like below. I need the macro to select only four of the columns for one sheet and three for the other,
and transpose the data leaving a space in between going vertical. To select which sheet it is going to
I want to select from the overall rating column, it will need to have a 7 or 6 to go to one page and a 1 or 2
to go to the other page. I have used the code from the following page:http://www.mrexcel.com/forum/excel-...-rows-paste-transpose-into-single-column.html

Sub SubbyWays()
'eaxample at http://www.mrexcel.com/forum/excel-...-rows-paste-transpose-into-single-column.html
Dim sData As Variant
Dim f_Data As Variant
Dim iC As Integer
Dim iR As Long
Dim rCoun As Long
Dim UCol As Integer
rCoun = 1
'Insert If statement to find top 2
sData = Range("A1").CurrentRegion.Value 'retrieves all values in the current row and stores in sdata
UCol = UBound(sData, 2) 'Ucol is set to equal Ubound(array name,Dimension) gives the upper boundry of the 2nd dimenson which would be how many colums are there.
ReDim f_Data(1 To UBound(sData, 1) * UCol, 1 To 1)

For iR = LBound(sData, 1) To UBound(sData, 1)
For iC = LBound(sData, 2) To UBound(sData, 2)
f_Data(rCoun, 1) = sData(Int((iC - 1) / UCol) + iR, 1 + ((iC - 1) Mod UCol))
rCoun = rCoun + 1
Next
Next iR
Range("H1").Resize(UBound(f_Data), 1) = f_Data
End Sub


How do I insert a loop to make sure for "SheetA" I get only the rows that have a 7 or 6
in the overall rating column? SheetB will have data that has a 1 or 2. Both sheets will have data transposed like in the example.


SheetA<o:p></o:p>
SheetB<o:p></o:p>
Other purpose<o:p></o:p>
Other purpose<o:p></o:p>
<o:p></o:p>
Other purpose<o:p></o:p>
Name <o:p></o:p>
Jim <o:p></o:p>
Name <o:p></o:p>
Jumior<o:p></o:p>
Account number <o:p></o:p>
1234<o:p></o:p>
Account number <o:p></o:p>
1112<o:p></o:p>
Comments<o:p></o:p>
Need to do this <o:p></o:p>
Comments<o:p></o:p>
Excellent works great <o:p></o:p>
Overall rating <o:p></o:p>
7<o:p></o:p>
Other purpose<o:p></o:p>
Other purpose<o:p></o:p>
Name <o:p></o:p>
Janet<o:p></o:p>
Other purpose<o:p></o:p>
Account number <o:p></o:p>
1111<o:p></o:p>
Other purpose<o:p></o:p>
<o:p></o:p>
Comments<o:p></o:p>
Excellent works great <o:p></o:p>
Name <o:p></o:p>
Jill<o:p></o:p>
Account number <o:p></o:p>
5678<o:p></o:p>
Comments<o:p></o:p>
This needs to be fixed <o:p></o:p>
Other purpose<o:p></o:p>
Overall rating <o:p></o:p>
6<o:p></o:p>
Name <o:p></o:p>
Joe<o:p></o:p>
Other Purpose<o:p></o:p>
Account number <o:p></o:p>
3214<o:p></o:p>
Comments<o:p></o:p>
Excellent works great<o:p></o:p>
Other purpose<o:p></o:p>
<o:p></o:p>
Other Purpose<o:p></o:p>
Other purpose<o:p></o:p>
Name <o:p></o:p>
Bob<o:p></o:p>
Name <o:p></o:p>
Bob<o:p></o:p>
Account number <o:p></o:p>
4321<o:p></o:p>
Account number <o:p></o:p>
4321<o:p></o:p>
Comments<o:p></o:p>
Excellent works great <o:p></o:p>
Comments<o:p></o:p>
Excellent works great <o:p></o:p>
Overall rating <o:p></o:p>
1<o:p></o:p>
Other Purpose<o:p></o:p>

<tbody>
</tbody>
 

Some videos you may like

Excel Facts

Format cells as currency
Select range and press Ctrl+Shift+4 to format cells as currency. (Shift 4 is the $ sign).

Watch MrExcel Video

Forum statistics

Threads
1,122,469
Messages
5,596,316
Members
414,053
Latest member
Dual Showman

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
Top