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

Please Login or Register  to view this content.

In attached file you wil find working example.

Best Regards
MaczaQ

Million Thanks MaczaQ, it work like charm :D