You changed patterns--once you went down column B, then to column C (for
batman).

But for robin, you went across per row.

This goes across row by row:

Option Explicit
Sub testme01()

Dim curWks As Worksheet
Dim newWks As Worksheet
Dim FirstRow As Long
Dim LastRow As Long

Dim RngToCopy As Range
Dim DestCell As Range
Dim iRow As Long
Dim oRow As Long

Set curWks = Worksheets("sheet1")
Set newWks = Worksheets.Add

With curWks
FirstRow = 2
LastRow = .Cells(.Rows.Count, "A").End(xlUp).Row

oRow = 0
For iRow = FirstRow To LastRow
If .Cells(iRow, 1).Value = .Cells(iRow - 1, 1).Value Then
'same key, apend to the far right
Set RngToCopy = .Range(.Cells(iRow, 2), _
.Cells(iRow, .Columns.Count).End(xlToLeft))
With newWks
Set DestCell = .Cells(oRow, .Columns.Count) _
.End(xlToLeft).Offset(0, 1)
End With
Else
oRow = oRow + 1
Set RngToCopy = .Rows(iRow)
Set DestCell = newWks.Cells(oRow, 1)
End If

RngToCopy.Copy _
Destination:=DestCell
Next iRow
End With

newWks.UsedRange.Columns.AutoFit

End Sub

Add headers to the first sheet if you don't have them already.

"Danny Boy via OfficeKB.com" wrote:

I have managed to produce my first macro

The only problem is the layout, I wonder if any one has a script for the
following

BATMAN CAPER WINGS
BATMAN LEATHER CAROL SONGERS
BATMAN SPONGE BATS SCARE ME
ROBIN PUFF THE
ROBIN MAGIX DRAGON
ROBIN LIVES IN THE BRONX

Above is the original layout (not the content, I made that up :-))

I want a macro to output it like:

Batman CAPER LEATHER SPONGE WINGS
CAROL SINGERS BATS SCARE ME
Robin PUFF THE MAGIX
DRAGON LIVES IN THE BRONX

I know that this can be done in a pivot table, but thats not the way I want
to do it!!!

Anyone help me pleeeassse

--
Message posted via OfficeKB.com
http://www.officekb.com/Uwe/Forums.a...mming/200601/1

--

Dave Peterson