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" <reply@newsgroup.please> wrote in message
news:df91nk$jjo$1@textnews.euro.net...
> 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" <bob.phillips@notheretiscali.co.uk> schreef in bericht
news:OCeqWL6rFHA.2996@tk2msftngp13.phx.gbl...
> 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" <reply@newsgroup.please> wrote in message
> news:df91nk$jjo$1@textnews.euro.net...
>> 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" <reply@newsgroup.please> wrote in message
news:df9ap7$mb3$1@textnews.euro.net...
> 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" <bob.phillips@notheretiscali.co.uk> schreef in bericht
> news:OCeqWL6rFHA.2996@tk2msftngp13.phx.gbl...
> > 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" <reply@newsgroup.please> wrote in message
> > news:df91nk$jjo$1@textnews.euro.net...
> >> 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" <bob.phillips@notheretiscali.co.uk> schreef in bericht
news:OmO9yY7rFHA.2588@tk2msftngp13.phx.gbl...
> 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" <reply@newsgroup.please> wrote in message
> news:df9ap7$mb3$1@textnews.euro.net...
>> 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" <bob.phillips@notheretiscali.co.uk> schreef in bericht
>> news:OCeqWL6rFHA.2996@tk2msftngp13.phx.gbl...
>> > 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" <reply@newsgroup.please> wrote in message
>> > news:df91nk$jjo$1@textnews.euro.net...
>> >> 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" <reply@newsgroup.please> wrote in message
news:df9hcn$ocq$1@textnews.euro.net...
> 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" <bob.phillips@notheretiscali.co.uk> schreef in bericht
> news:OmO9yY7rFHA.2588@tk2msftngp13.phx.gbl...
> > 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" <reply@newsgroup.please> wrote in message
> > news:df9ap7$mb3$1@textnews.euro.net...
> >> 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" <bob.phillips@notheretiscali.co.uk> schreef in bericht
> >> news:OCeqWL6rFHA.2996@tk2msftngp13.phx.gbl...
> >> > 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" <reply@newsgroup.please> wrote in message
> >> > news:df91nk$jjo$1@textnews.euro.net...
> >> >> 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
> >> >>
> >> >>
> >> >
> >> >
> >>
> >>
> >
> >
>
>