This Code Runs but nothing happens.

[dccp8002]

The image below shows all the table records on the left, Cells B - F are the column headers and each red number is the rank.
I recorded a macro that selected rank 1 and transposed it into the table in B-F row 1.

I attempted to write this macro to loop through the column(A) with the records:

Sub Transpose_data()
'
' Transpose_Data Macro
' transpose the data from one many rows in column to rows in table
Dim Cll As Range
Dim Rng As Range
Dim Rng2 As Range
Set Rng2 = Range("A:A")
Set Rng = Range (Cells(1,1), Cells(1,6))
For Each Cll In Rng2
If IsNumeric(Rng2.Value) Then
Rng.Select
Selection.Copy
Range("B2").End(xlDown).Offset(1,0).Select
Selection.PasteSpecial Paste:=xlPasteALL, Operation:=xlNone, SkipBlanks:=_False, Transpose:=True
End If
Next Cll
End Sub

Thoughts? Any help is much appreciated.

 

[Tonmoymridha]

Range("B2").End(xlDown).Offset(1,0).Select

i think this line has problem.You should write something in (B1) and (B2) then You should run this code-Range("B1").End(xlDown).Offset(1,0).Select

 

[mole999]

if you can, the first line of code should also ensure you select the correct sheet

 

[Fluff]

How about

Sub dccp()
   Dim i As Long
   
   For i = 1 To Range("A" & Rows.Count).End(xlUp).Row Step 6
      Range("B" & Rows.Count).End(xlUp).Offset(1).Resize(, 6).Value = Application.Transpose(Range("A" & i).Resize(6).Value)
   Next i
End Sub

 

[dccp8002]

How about

Sub dccp()
   Dim i As Long
  
   For i = 1 To Range("A" & Rows.Count).End(xlUp).Row Step 6
      Range("B" & Rows.Count).End(xlUp).Offset(1).Resize(, 6).Value = Application.Transpose(Range("A" & i).Resize(6).Value)
   Next i
End Sub

Fluff,

There was a problem with this line:
"For i = 1 To Range("A" & Rows.Count).End(x1Up).Row Step 6"

it threw a Run-time error '1004':
Application - defined or object-defined error

 

[Fluff]

You have missed typed the code, it should be xlUp (lowercase L) not x1Up

 

[dccp8002]

Ugh...::smh:: one of those days, thank you!

 

[dccp8002]

How about

Sub dccp()
   Dim i As Long
  
   For i = 1 To Range("A" & Rows.Count).End(xlUp).Row Step 6
      Range("B" & Rows.Count).End(xlUp).Offset(1).Resize(, 6).Value = Application.Transpose(Range("A" & i).Resize(6).Value)
   Next i
End Sub

This worked! Thank you for the help!
Always remember the "lowercase l" in xlUp and not a "1".

 

[Fluff]

You're welcome & thanks for the feedback.