Copy X lines down, and paste transpose

[koi]

Hi All,

i need the macro to copy x lines down based on the input in helper column, and then paste transpose it to another cell.

example in D2 the number is 3, then the macro will copy B2-B4 (3 lines down), then paste transpose it in E2:G2

then it search again in D2:D to find another number..example 4, then it copied B5:B8 and copy transpose it in E5:H5

Thanks for Helping.

 

[Marc L]

Hi !

A starter demonstration to paste in the worksheet module :​

Sub Demo1()
         Dim Rg As Range
    For Each Rg In Me.UsedRange.Columns(4).SpecialCells(xlCellTypeConstants)
        If IsNumeric(Rg.Value) Then Rg(1, 2).Resize(, Rg.Value).Value = Application.Transpose(Rg(1, -1).Resize(Rg.Value))
    Next
End Sub

Do you like it ? So thanks to click on bottom right Like !
​

 

[koi]

Thanks a lot Marc,

but i got error code: "invalid use of Me keyword"

any idea why? or probably we need to specify the sheet name?

 

[koi]

Thanks Marc,

didn't read quite well that i supposed to put it in the sheet module, it works like a charm !! again thank you !

 

[Marc L]

As Me statement is equal to the worksheet of the module …
In a standard module you must replace it with a worksheet reference.

Notice column D is useless with column A as a key
except if D9 cell content wasn't a typo …
​

 

[Marc L]

Demonstration with column A as key (if column D useless) :​

Sub Demo2()
    Dim R&, N&
        R = Me.UsedRange.Rows.Count + 1
  While R > 2
        N = R
        R = Cells(R, 1).End(xlUp).Row
        N = N - R
        Cells(R, 5).Resize(, N).Value = Application.Transpose(Cells(R, 2).Resize(N))
  Wend
End Sub

You may Like it !
​