Copy multiple rows and paste transpose into a single column

[bobby_smith]

Good day,

I would like some assistance in creating a macro that will copy multiple rows of data, and then transpose and paste into a single column.
I've attached a an image of what I'm talking about. Colums A to G has the raw data. Column J has the format I'm trying to get.
The colors are not important (I used colors to hopefully highlight what I'm trying to explain).

As you can see, I have data in different rows, then I copied and pasted each row in a single column in that order.

I would like the macro to be in such away that it select all the information in a continuous row, paste in a column and then move to next row.

I was able to select and paste, but I was not able to get the macro to return the following row an repeat the task.

This is what I have

Sub macro1()
Range("B2").Select
Range(Selection, Selection.End(xlToRight)).Select
Selection.Copy
For n = 0 To 100
ActiveCell.Offset(n * 11, 15).Range("a1").Select
Selection.PasteSpecial Paste:=xlPasteAll, skipblanks:=False, Transpose:=True

Any assistance would be greatly appreciated.

Excel 2010

	A	B	C	D	E	F	G	H	I	J

<tbody>
[TD="align: center"]1[/TD]
[TD="bgcolor: #C5D9F1, align: right"]6903[/TD]
[TD="bgcolor: #C5D9F1, align: right"]6613[/TD]
[TD="bgcolor: #C5D9F1, align: right"]6908[/TD]
[TD="bgcolor: #C5D9F1, align: right"]6816[/TD]
[TD="bgcolor: #C5D9F1, align: right"]6727[/TD]
[TD="bgcolor: #C5D9F1, align: right"]6671[/TD]
[TD="bgcolor: #C5D9F1, align: right"]6613[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="bgcolor: #B8CCE4, align: right"]6903[/TD]

[TD="align: center"]2[/TD]
[TD="bgcolor: #DA9694, align: right"]6903[/TD]
[TD="bgcolor: #DA9694, align: right"]5753[/TD]
[TD="bgcolor: #DA9694, align: right"]6086[/TD]
[TD="bgcolor: #DA9694, align: right"]6069[/TD]
[TD="bgcolor: #DA9694, align: right"]5931[/TD]
[TD="bgcolor: #DA9694, align: right"]5907[/TD]
[TD="bgcolor: #DA9694, align: right"]5753[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="bgcolor: #B8CCE4, align: right"]6613[/TD]

[TD="align: center"]3[/TD]
[TD="bgcolor: #C4D79B, align: right"]6816[/TD]
[TD="bgcolor: #C4D79B, align: right"]5614[/TD]
[TD="bgcolor: #C4D79B, align: right"]5577[/TD]
[TD="bgcolor: #C4D79B, align: right"]5635[/TD]
[TD="bgcolor: #C4D79B, align: right"]5617[/TD]
[TD="bgcolor: #C4D79B, align: right"]5499[/TD]
[TD="bgcolor: #C4D79B, align: right"]5614[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="bgcolor: #B8CCE4, align: right"]6908[/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="bgcolor: #B8CCE4, align: right"]6816[/TD]

[TD="align: center"]5[/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="bgcolor: #B8CCE4, align: right"]6727[/TD]

[TD="align: center"]6[/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="bgcolor: #B8CCE4, align: right"]6671[/TD]

[TD="align: center"]7[/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="bgcolor: #B8CCE4, align: right"]6613[/TD]

[TD="align: center"]8[/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="bgcolor: #DA9694, align: right"]6903[/TD]

[TD="align: center"]9[/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="bgcolor: #DA9694, align: right"]5753[/TD]

[TD="align: center"]10[/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="bgcolor: #DA9694, align: right"]6086[/TD]

[TD="align: center"]11[/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="bgcolor: #DA9694, align: right"]6069[/TD]

[TD="align: center"]12[/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="bgcolor: #DA9694, align: right"]5931[/TD]

[TD="align: center"]13[/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="bgcolor: #DA9694, align: right"]5907[/TD]

[TD="align: center"]14[/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="bgcolor: #DA9694, align: right"]5753[/TD]

[TD="align: center"]15[/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="bgcolor: #C4D79B, align: right"]6816[/TD]

[TD="align: center"]16[/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="bgcolor: #C4D79B, align: right"]5614[/TD]

[TD="align: center"]17[/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="bgcolor: #C4D79B, align: right"]5577[/TD]

[TD="align: center"]18[/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="bgcolor: #C4D79B, align: right"]5635[/TD]

[TD="align: center"]19[/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="bgcolor: #C4D79B, align: right"]5617[/TD]

[TD="align: center"]20[/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="bgcolor: #C4D79B, align: right"]5499[/TD]

[TD="align: center"]21[/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="bgcolor: #C4D79B, align: right"]5614[/TD]

</tbody>

Sheet1​

 

[KB_Miner]

You should be be able to do this with a For Loop. Create a variable (for short code like this, I just usually use X instead of trying to name it something), then use that variable to control your loop. So

Dim X as Integer
X = 2
For X = 2 to Last row of your data
Range("B" & X).Select
'rest of your code here
Next X

Will need some code to determine where the next starting point of your column J will be, but that shouldn't be too difficult. Would probably need another variable to control that. Also, will need to change Last row of your data to either a number, or a variable that helps you determine what your last row is.

 

[VBA Geek]

returned in column J

Sub SubbyWays()
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
sData = Range("A1").CurrentRegion.Value
UCol = UBound(sData, 2)
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("J1").Resize(UBound(f_Data), 1) = f_Data
End Sub

 

[bobby_smith]

VBA Geek,

The code worked perfect.

Thank you very much for the taking the time to assist me.

I'm truly thankful.

 

[bobby_smith]

KB_Miner,

Thank you for your suggestion.
Very much appreciated.

 

[gibbonsm]

KB_Miner,

Thank you for your suggestion.
Very much appreciated.

In this example I understand how it works, but lets say I only want to copy and move the row if column e contains a 6 or 7 and then I would want to move it to another sheet.

Thanks in advance for the help.