Thank you for your feedback.

Keiji

Elton Law wrote:
Hi Keiji,
I have tested. Much better than I expect. That's really great. Tks indeed !

"keiji kounoike" <"kounoike AT mbh.nifty." wrote:

Try this one. I presume date is populated in column A.

Sub copydatatest()
Dim srcsh As Worksheet, dstsh As Worksheet
Dim datarng As Range, Prng As Range, dstrng As Range
Set srcsh = ActiveSheet
Set dstsh = Worksheets.Add(after:=srcsh)
srcsh.Select

Set daterng = srcsh.Columns("A").SpecialCells(xlCellTypeConstant s)

For Each Prng In daterng
Set fndrng = dstsh.Columns("A").Find(Prng.Value, lookat:=xlWhole)
If fndrng Is Nothing Then
Set dstrng = dstsh.Cells(Rows.Count, "A").End(xlUp)
If dstrng.Value < "" Then
Set dstrng = dstrng.Offset(1, 0)
End If
Prng.Resize(, 5).Copy Destination:=dstrng
Else
Set dstrng = fndrng.End(xlToRight).Offset(0, 1)
On Error GoTo re
Prng.Offset(0, 1).Resize(, 4).Copy Destination:=dstrng
End If
Next
dstsh.Select
Exit Sub

'MsgBox "Error: Out of Range"
Range(Prng.Offset(0, 1), Prng.End(xlToRight)).Interior.ColorIndex = 6
Resume Next
End Sub

Keiji

Elton Law wrote:
Dear Expert,

Would like to transpose ...
Starting from 1 Jan 99, 2 Jan 99, 3 Jan 99 ..

That is ... after moving, Peter will be moved in same row as Elton. Peter
will show up after P4 .... Jasmine will show up in same row as Elton. Jasmine
will be on the left of E5

But number of rows of the same date may be different\, making difficult to
move ..

Befo
1-Jan-99 Elton A2 147 P4
1-Jan-99 Peter A1 157 E5
1-Jan-99 Jasmine A2 257 A1
1-Jan-99 Jenny A3 119 H8
2-Jan-99 Jasmine A6 123 G0
2-Jan-99 Jenny A0 111 B4
2-Jan-99 Kammi A8 345 D2
3-Jan-99 Patrick A3 159 D7
3-Jan-99 Dion A4 247 Y2

After: (Elton, Peter, Jasmine, Jenny are same row starting with 1 Jan 99.
Due to display problem here, cannot be seen easily)

1-Jan-99 Elton A2 147 P4 Peter A1 157 E5 Jasmine A2 257 A1 Jenny A3 119 H8
2-Jan-99 Jasmine A6 123 G0 Jenny A0 111 B4 Kammi A8 345 D2
3-Jan-99 Patrick A3 159 D7 Dion A4 247 Y2