Transpose data columns to rows for many work sheets using VBA code or micro ?

Question

Dear Microsoft users ,

i have many excel work sheets ( 100 or more ) in single excel file ( Microsoft office 2013 ) , i would like to convert all columns to rows and rows to columns , 

i have read and seen many ways but need to do that by copy and past ( using past options ) but this way its not efficient to me ,

so is there any VBA code can do that for all this sheet by single click_event ?

thanks for suggestion and solution ..

Follow my data set format ... 

 I would to convert all sheets to following formats ... for all 100 sheets ?

Answer 1

1. Make a backup of your workbook. 
2. Open your workbook and ALT+F11
3. Locate your Workbook name in Project Explorer Window
4. Right click on your workbook name > Insert > Module 
5. Copy paste the Macro code given
6. Go back to your Workbook and ALT+F8 to display Macro Window
7. Run your Macro from here
8. Delete you Macro if the Macro was needed to be run only once.
9. Otherwise save your file as .xlsm if you intend to reuse Macro again.

'*** Macro Starts

Sub TransposeCopy()

    Dim Ws As Worksheet, Ws2 As Worksheet

    Application.ScreenUpdating = False

    Application.DisplayAlerts = False

    On Error Resume Next

    Worksheets("#TempSheet#").Delete

    On Error GoTo 0

    Worksheets.Add.Name = "#TempSheet#"

    Set Ws2 = Worksheets("#TempSheet#")

    For Each Ws In Worksheets

        If Ws.Name <> "#TempSheet#" Then

            Ws2.Cells.Clear

            Ws.UsedRange.Copy

            Ws2.Range("A1").PasteSpecial Transpose:=True

            Ws.Cells.Clear

            Ws2.UsedRange.Copy Ws.Range("A1")

        End If

    Next Ws

    Ws2.Delete

    Application.CutCopyMode = False

    Application.DisplayAlerts = True

    Application.ScreenUpdating = True

End Sub

Answer 2

Here's a solution using the formulas

{=TRANSPOSE(A1:G8)}   ctrl +shift+enter

or

=IFERROR(INDEX($A$1:$G$8;COLUMN(A2);ROW(A2));"")

Answer 3

Very thank you for your great code , really this help me more , Thank you very much sir ,

Answer 4

okay,

your issue has been solved