Moving rows to columns

[Jan Verten]

Hello group,

I have an Excel sheet from which I need to automaticaly generate a new
worktab (is that the correct translation to English?) which does some format
editing. In the current sheet I have the data lay-out as follow:

X A1 A2 A3 A4 A5
X B1 B2 B3 B4 B5
etc.

Y C1 C2 C3 C4 C5
Y D1 D2 D3 D4 D5
etc.

I need to move that to:
X A1 A2 A3 A4 A5 B1 B2 B3 B4 B5 etc.
Y C1 C2 C3 C4 C5 D1 D2 D3 D4 D5 etc.

Is there any way to do this? I need to keep the old format for human
editing, the sheet with the new format should be generated automaticaly
(perhaps using a macro?)?

Please help!

Thank you very much, Jan Verten

 

[Bob Phillips]

Sub Test()
Dim iLastRow As Long
Dim i As Long
Dim j As Long
Dim iRow As Long
Dim iCol As Long
Dim sh As Worksheet
Dim sTemp As String

Set sh = ActiveSheet
iLastRow = Cells(Rows.Count, "A").End(xlUp).Row
Worksheets.Add.Name = "NewData"
iRow = 0
sTemp = ""
For i = 1 To iLastRow
If sh.Cells(i, "A").Value <> sTemp Then
iRow = iRow + 1
sh.Rows(i).Copy Cells(iRow, "A")
sTemp = sh.Cells(i, "A").Value
Else
j = sh.Cells(i, Columns.Count).End(xlToLeft).Column
iCol = Cells(iRow, Columns.Count).End(xlToLeft).Column
sh.Cells(i, "B").Resize(, j - 1).Copy _
Cells(iRow, iCol + 1)
End If
Next i

End Sub

--

HTH

RP
(remove nothere from the email address if mailing direct)

 

[Jan Verten]

Thank you very much, this is indeed what I need! The only problem is that it
edits the current sheet (although it creates an empty 'NewData' sheet), what
do I need to change to let it put its results in 'NewData'? Thanks again!

Jan Verten.

 

[Bob Phillips]

No, it creates NewData and populates that, leaving the original intact.

If you do it a second time there is no error checking for NewData already
existing, so you need to manually delete.

--

HTH

RP
(remove nothere from the email address if mailing direct)

 

[Jan Verten]

It really doesn't work that way over here. I'm using Excel 2000, if that
makes any difference. Do you want me to send you the excel sheet so you can
see it in action?

Jan Verten.

 

[Bob Phillips]

Yeah do. I also am using Excel 2000 so it isn't that.

--

HTH

RP
(remove nothere from the email address if mailing direct)