Transpose into new row in next page

  • Friends

    I want to transpose the variable columns to single column in the next page like

    Original

    A1,A2,A3,A4

    B1,B2,B3

    C1,C2,C3,C4,C5

    ...


    To next page 'Final' like

    A1

    A2

    A3

    A4

    B1

    B2

    B3

    C1

    C2

    C3

    C4

    C5

    ...


    One by one


    Normal transpose doesn't work for me.


    Thx in advance:thumbup:

  • Are those the actual ranges you want to copy to "Final"? If not, please clarify.

    You can say "THANK YOU" for help received by clicking the :thumbup: icon in the bottom right corner of the helper's post.
    Practice makes perfect. I am very far from perfect so I'm still practising.

  • You could try this. Change references where required.

  • I think this is what you want to do, the code could be more efficient if bypassing the clipboard copyandpaste, but I am not sure how to do the transpose in that case.





    Regards

    Justin

  • You don't need to switch off ScreenUpDating if you don't select the sheets. If you do then you must switch it back on again. Try this


  • An alternative solution is to use Power Query. Load your range to PQ. Highlight all columns and click on Unpivot. The Mcode follows


    Code
    let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Column1", type text}, {"Column2", type text}, {"Column3", type text}, {"Column4", type text}, {"Column5", type text}}),
    #"Unpivoted Columns" = Table.UnpivotOtherColumns(#"Changed Type", {}, "Attribute", "Value"),
    #"Removed Columns" = Table.RemoveColumns(#"Unpivoted Columns",{"Attribute"})
    in
    #"Removed Columns"

Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!