Moving rows to columns
 

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

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" wrote in message
...
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

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" schreef in bericht
...
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" wrote in message
...
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

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" wrote in message
...
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" schreef in bericht
...
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" wrote in message
...
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

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" schreef in bericht
...
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" wrote in message
...
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" schreef in bericht
...
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" wrote in message
...
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

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

--

HTH

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


"Jan Verten" wrote in message
...
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" schreef in bericht
...
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" wrote in message
...
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" schreef in bericht
...
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" wrote in message
...
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