row to column - vba or code or formula

[Vishaal]

we have the following data and we have copy the sheet1!B2:C2 and pasted on Sheet2!B2 and transposed it, again we have copied the data from Sheet1!B3:D3 and again pasted on Sheet2!B3

we will copy the data and check last filled row in sheet2 and paste transposed data

in sheet1 data may be long B2:AZ2

Book11
	B	C	D	E
2	ST1	ST2
3	TT2	TT4	TT6
4	TT98	TT4	TT6	TT2
Sheet1

Required as per below sheet

Book11
	B
2	ST1
3	ST2
4	TT2
5	TT4
6	TT6
7	TT98
8	TT4
9	TT6
10	TT2
Sheet2

 

[Kerryx]

Power Query

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

Book1
	A	B	C	D
1	Column1	Column2	Column3	Column4
2	ST1	ST2
3	TT2	TT4	TT6
4	TT98	TT4	TT6	TT2
5
6	Value
7	ST1
8	ST2
9	TT2
10	TT4
11	TT6
12	TT98
13	TT4
14	TT6
15	TT2
Sheet1

 

[Vishaal]

I have not used power query code, can we get vba