spreadsheet manipulation type question

[Stereo Steve]

We're having a little bit of trouble getting data from sage into our DHL easyship software. Essentially you can 'print' data to a file and choose the format in sage but choosing csv means you end up with mismatched columns and data all over the shop. No good. We can export as XML and then macro in excell to end up with a column of pure data, all perfect and all in the right places but it's vertical, not horizontal. So, say you have Name, Phone, Address 1,2,3, Postcode as your required fields, we can get them arranged down the way in column A. Like

A

1 Bob
2 01666 666666
3 bleak house
4 bodmin moor
5 UK
6 EX5 6PT
7 Dave
8 01555 675844
9 22 Acacia Avenue
10 Wembley
11 London
12 W3 7TH

And so on. What I want to do is create a macro to get A1 to A6 into A1,B1,C1,D1,E1,F1 and then A7 to 12 into A2,B2,C2,D2,E2,F2 and so on ad infinitum. So essentially we drag each 6 rows out into 1 row and then the next 6 onto the row below etc.

Can anyone think of an easy way to macro this or is going to be a case of mapping it line by line, keeping in mind there may be 500 addresses in one sheet!

[Duncan Rounding]

This is very easy to do using an Excel macro.

Here's an example of one that does what you want by transposing twelve repetitive rows into columns:

Sub Macro1()
'
' Macro1 Macro
' Macro recorded 13/08/2008 by Duncan Rounding
'

'
    ActiveCell.Range("A1:A12").Select
    Selection.Copy
    ActiveCell.Offset(0, 1).Range("A1").Select
    Selection.PasteSpecial Paste:=xlPasteAll, Operation:=xlNone, SkipBlanks:= _
        False, Transpose:=True
    ActiveCell.Offset(1, 0).Rows("1:11").EntireRow.Select
    Application.CutCopyMode = False
    Selection.Delete Shift:=xlUp
    ActiveCell.Select
End Sub

Just create any macro then go and edit it and insert this code in it's place.

[Stereo Steve]

Thanks Duncan, I knew there had to be an easy solution, rather than creating some beast of a macro that takes 20 minutes to run! I'll have a play with this later. Much appreciated.

[Duncan Rounding]

As it stands this needs to be run manually for each set of 12 but you can easily copy and paste the code to do at least 20 or 30 at a time. After you've completely finished you'll also have to delete the first column.