Macro to copy to another sheet in one line horizontally

Question

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 subsequce ?

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

It might be worth you recording a macro while you the above manually just once or twice. Then you will have the basis for the code and will probably only need to make a few changes. — Saladin Akara
the problem might be the number of row in the table might be growing over time.. :( — eure

Lance Roberts Lance Roberts · Accepted Answer · 2011-07-24T10:46:40

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

Macro to copy to another sheet in one line horizontally

1 Answers