Sub Test()
Dim iLastRow As Long
Dim i As Long
Dim j As Long
Dim iRow As Long
Dim iCol As Long
Dim sh As Worksheet
Dim sTemp As String

Set sh = ActiveSheet
iLastRow = Cells(Rows.Count, "A").End(xlUp).Row
Worksheets.Add.Name = "NewData"
iRow = 0
sTemp = ""
For i = 1 To iLastRow
If sh.Cells(i, "A").Value <> sTemp Then
iRow = iRow + 1
sh.Rows(i).Copy Cells(iRow, "A")
sTemp = sh.Cells(i, "A").Value
Else
j = sh.Cells(i, Columns.Count).End(xlToLeft).Column
iCol = Cells(iRow, Columns.Count).End(xlToLeft).Column
sh.Cells(i, "B").Resize(, j - 1).Copy _
Cells(iRow, iCol + 1)
End If
Next i

End Sub

--

HTH

RP
(remove nothere from the email address if mailing direct)


"Jan Verten" <reply@newsgroup.please> wrote in message
news:df91nk$jjo$1@textnews.euro.net...
> Hello group,
>
> I have an Excel sheet from which I need to automaticaly generate a new
> worktab (is that the correct translation to English?) which does some
format
> editing. In the current sheet I have the data lay-out as follow:
>
> X A1 A2 A3 A4 A5
> X B1 B2 B3 B4 B5
> etc.
>
> Y C1 C2 C3 C4 C5
> Y D1 D2 D3 D4 D5
> etc.
>
> I need to move that to:
> X A1 A2 A3 A4 A5 B1 B2 B3 B4 B5 etc.
> Y C1 C2 C3 C4 C5 D1 D2 D3 D4 D5 etc.
>
> Is there any way to do this? I need to keep the old format for human
> editing, the sheet with the new format should be generated automaticaly
> (perhaps using a macro?)?
>
> Please help!
>
> Thank you very much, Jan Verten
>
>