command button (code) to transpose datas from columns to rows.

[rabindra]

Hello,

I need help!!

I have hundreds of thousands of data in column A and their values are also different. So, I want to be able to select a range manually and transpose the selected range to rows in a new worksheet every time I click the Command Button.

Need help with the command button code / Macro OR perhaps an easier way for the solution.

Thanks!!

 

[rabindra]

Works wonders AlphaFrog.

What would be the code if I want to do the same in a new workbook instead of a new worksheet?

Also, say I transposed 100 datas and closed the file and when I re-open the file again, I would still like to continue pasting to where I left off. Currently, the transposed data opens in new sheet when I resume later on.

There may be more questions in future AlphaFrog, I appreciate your help.

Each time you open the workbook, this will add a new sheet only once.

[COLOR=darkblue]Private[/COLOR] [COLOR=darkblue]Sub[/COLOR] CommandButton1_Click()
    [COLOR=darkblue]Static[/COLOR] ws [COLOR=darkblue]As[/COLOR] Worksheet
    Selection.Copy
    [COLOR=darkblue]If[/COLOR] ws [COLOR=darkblue]Is[/COLOR] [COLOR=darkblue]Nothing[/COLOR] [COLOR=darkblue]Then[/COLOR]
        [COLOR=darkblue]Set[/COLOR] ws = Sheets.Add
        ws.Range("A1").PasteSpecial _
            Paste:=xlPasteAll, Operation:=xlNone, SkipBlanks:=False, Transpose:=[COLOR=darkblue]True[/COLOR]
    [COLOR=darkblue]Else[/COLOR]
        ws.Range("A" & Rows.Count).End(xlUp).Offset(1).PasteSpecial _
            Paste:=xlPasteAll, Operation:=xlNone, SkipBlanks:=False, Transpose:=[COLOR=darkblue]True[/COLOR]
        ws.Select
    [COLOR=darkblue]End[/COLOR] [COLOR=darkblue]If[/COLOR]
End [COLOR=darkblue]Sub[/COLOR]