Automate transpose column data into row

[saurabhS]

I have a workbook in which I have almost 150 sheet. In each sheet I have data in column like in range A1:AX1. I want to transpose that data in rows in same sheet. Just to say in each sheet the range is not fix. I want to develop a maro which will transpose my data from columns to rows for all the 150 sheets data.

PLease see the data I have in sheet as example
Sheet 1: Here I want the

sheet 2 :-

 

[alansidman]

Book5
	A	B	C	D	E	F	G	H
1		Column1	Column2	Column3	Column4	Column5	Column6
2		A	B	C	D	E	F
3		g	h	i	j	k	l
4
5	Column1	Column2
6	A	g
7	B	h
8	C	i
9	D	j
10	E	k
11	F	l
Sheet1

resolved using Power Query

let
    Source = Excel.CurrentWorkbook(){[Name="Table2"]}[Content],
    #"Transposed Table" = Table.Transpose(Source)
in
    #"Transposed Table"

 

[alansidman]

Second scenario

Book5
	A	B	C	D	E	F
1		Column1	Column2	Column3	Column4	Column5
2		A	B	C	D	F
3
4	Value
5	A
6	B
7	C
8	D
9	F
Sheet2

With Power Query once again

let
    Source = Excel.CurrentWorkbook(){[Name="Table4"]}[Content],
    #"Unpivoted Columns" = Table.UnpivotOtherColumns(Source, {}, "Attribute", "Value"),
    #"Removed Columns" = Table.RemoveColumns(#"Unpivoted Columns",{"Attribute"})
in
    #"Removed Columns"