Transpose rows in 2 columns and ordering

[stakar]

Hi there!
I just want to ask something that i can't manage.
I have rows of 6 cells per row, that i want to be copied to another sheet in the same workbook but the 1st cell must be copied until the rest 5 numbers ends and then to be ordered asceding, according to the 1st cell
Every week i 'll add, in different dates, 2 more range of raws so the macro must transpose the new rows to new column, and do the ordering including all the previous entries.
eg.

sheet 1
123---1--2--3--4--5
124---6--7--8--9--10

sheet2 (after the orderding)
124--1
124--2
124--3
124--4
124--5
123--6
123--7
123--8
123--9
123-10

Thanks in advance

 

[stakar]

I forgot to mention that if i make a mistake and i want to change a value how the macro handling this? Because it is a problem

Thanks once more
Stathis

 

[stakar]

up

 

[mirabeau]

hi,

would you like to try this?

if you want to change anything, or add values, then just run the macro again

Sub transpozeroze()
Dim a As Variant, c() As Variant, nr As Long
Dim i As Long, j As Long, k As Long
a = Sheets("sheet1").Range("A1").CurrentRegion.Resize(, 6)
nr = UBound(a, 1)
ReDim c(1 To nr * 5, 1 To 2)
For i = 1 To nr
    For j = 2 To 6
        k = k + 1
        c(k, 1) = a(i, 1)
        c(k, 2) = a(i, j)
Next j, i
With Sheets("sheet2").Cells(1).Resize(k, 2)
    .Cells = c
    .Resize(, 1).Sort .Cells(1), 2, Header:=xlNo
End With
End Sub

 

[stakar]

Mirabeau you are incredible!!
Exactly what i wanted!
One more favour , my mistake i didnt mention it.
Im using the 1st row of the sheet1 and of the sheet2 as headers. I tried to make your macro to work but i fix only the sheet1 changing the

a = Sheets("sheet1").Range("A1").CurrentRegion.Resize(, 6) to
a = Sheets("sheet1").Range("A2").CurrentRegion.Resize(, 6)

but with the sheet2 i had no luck

I appreciate if you can change your code for the headers
Thanks in advance
Stathis

 

[mirabeau]

Mirabeau you are incredible!!
Exactly what i wanted!
One more favour , my mistake i didnt mention it.
Im using the 1st row of the sheet1 and of the sheet2 as headers. I tried to make your macro to work but i fix only the sheet1 changing the

a = Sheets("sheet1").Range("A1").CurrentRegion.Resize(, 6) to
a = Sheets("sheet1").Range("A2").CurrentRegion.Resize(, 6)

but with the sheet2 i had no luck

I appreciate if you can change your code for the headers
Thanks in advance
Stathis

I'm not entirely clear about what you ask - a worksheet example may help.

But, guessing, if you change the code as indicated in red, does it do something like you want?

Sub transpozeroze()
Dim a As Variant, c() As Variant, nr As Long
Dim i As Long, j As Long, k As Long
a = Sheets("sheet1").Range("A1").CurrentRegion.Resize(, 6)
nr = UBound(a, 1)
ReDim c(1 To nr * 5, 1 To 2)
For i = 1 To nr
    For j = 2 To 6
        k = k + 1
        c(k, 1) = a(i, 1)
        c(k, 2) = a(i, j)
Next j, i
With Sheets("sheet2").Cells(2, 1).Resize(k, 2)
    .Cells = c
    .Resize(, 1).Sort .Cells(1), 2, Header:=xlNo
End With
End Sub

 

[stakar]

Excellent!!
What i wanted. On the sheet2 you start the data from the 2nd row and now i can have the headers on the 1st row!!
Maribeau thanks a lot
Keep on going!! You have the skills!

 

[mirabeau]

Excellent!!
What i wanted. On the sheet2 you start the data from the 2nd row and now i can have the headers on the 1st row!!
Maribeau thanks a lot
Keep on going!! You have the skills!

So good!

I'm happy that all worked out for you.