Moving rows to columns


J

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
 
Ad

Advertisements

B

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)
 
J

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.
 
B

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)
 
J

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.
 
Ad

Advertisements

B

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)
 
Ad

Advertisements


Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top