Macro to copy data to another sheet in one line - horizontally

Wondering if there is way to copy data from one excel sheet in table format and transform into a single line of data via macro for excel 2007?

For instance, I have got following data in table format at Sheet1;

A - B - C
Date - Name - Amount
02/03/2011 - John - -20.00
05/03/2011 - Peter - 30.89
05/03/2011 - Anthony - 988.00
.....
..
.


The macro should eventually will copy data above to another excel sheet (Sheet2) with horizontally (transpose) one continuous with another with added '@' as a separator.

02/03/2011
John
-20.00
@
05/03/2011
Peter
30.89
@
05/03/2011
Anthony
988.00
...
..
.


can this be done?

The recorded macro will look like this;

Sheets("Sheet1").Select
Range("A2:C2").Select
Selection.Copy
Sheets("Sheet2").Select
Selection.PasteSpecial Paste:=xlPasteAll, Operation:=xlNone, SkipBlanks:= _ False, Transpose:=True

Following code should cover your needs

Private Sub CommandButton1_Click()
Dim r, sr, destCol As Long
r = 2       'source row index
destCol = 2 'destination column index
sr = 1      'destination row index
    Do Until Cells(r, 1).Value = ""
      arr = Split(Cells(r, 1).Value, " - ")
      On Error Resume Next
      sr = Columns(destCol).Find("*", searchDirection:=xlPrevious).Row + 1
      Debug.Print sr
      Cells(sr, destCol).Resize(UBound(arr) + 1, 1).Value = Application.Transpose(arr)
      Cells(sr + UBound(arr) + 1, destCol).Value = "@"
      r = r + 1
    Loop
End Sub

In attached file you wil find working example.

Best Regards
MaczaQ

Million Thanks MaczaQ, it work like charm :D