Macro to copy 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;

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 with horizontally (transpose) one continuously 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?

I only could work for 1st line, how could I continue with subsequent?

Range("A2:F2").Select
    Selection.Copy
    Sheets("Sheet2").Select
    Selection.PasteSpecial Paste:=xlPasteAll, Operation:=xlNone, SkipBlanks:= _
        Fa开发者_C百科lse, Transpose:=True

---

You need to make a loop, something like

Dim workingrow As Range
For Each workingrow in UsedRange.Rows
  workingrow.Columns("A:F").Copy
  Sheets("Sheet2").Range("whereveryouwant").PasteSpecial Paste:=xlPasteAll, _
       Operation:=xlNone, SkipBlanks:= False, Transpose:=True
Next workingrow