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!!

 

[AlphaFrog]

This code was generated by the macro recorder.

    Selection.Copy
    Sheets.Add
    Selection.PasteSpecial Paste:=xlPasteAll, Operation:=xlNone, SkipBlanks:= _
        [color=darkblue]False[/color], Transpose:=[color=darkblue]True[/color]

 

[rabindra]

Dear Alpha Frog,

Thank you for your prompt reply. Yet, I am still lost

Please let me know what am I doing wrong here.

I copied the code, inserted a command button (ActiveX Control) and pasted there. I then selected the range I wanted to copy n' when I clicked the command button, it's leading me no where.

Please advice.

 

[AlphaFrog]

I copied the code, inserted a command button (ActiveX Control) and pasted there. I then selected the range I wanted to copy n' when I clicked the command button, it's leading me no where.

You seem to have done it correctly. I just did those steps you outlined and it works for me. I don't know where we differ.

This is my command button procedure

[color=darkblue]Private[/color] [color=darkblue]Sub[/color] CommandButton1_Click()
    Selection.Copy
    Sheets.Add
    Selection.PasteSpecial Paste:=xlPasteAll, Operation:=xlNone, SkipBlanks:= _
        [color=darkblue]False[/color], Transpose:=[color=darkblue]True[/color]
[color=darkblue]End[/color] [color=darkblue]Sub[/color]

After you created the command button, did you toggle off design mode?

What exactly do you mean by "leading me no where"; Do you get a new sheet but no transposed data?

 

[rabindra]

This is what I have in Column A (eg.)

[TABLE="width: 449"]
<colgroup><col></colgroup><tbody>[TR]
[TD]Reid Music Ltd[/TD]
[/TR]
[TR]
[TD]1835 Topsail Rd, Mount Pearl, NL A1N 3J6 Get directions[/TD]
[/TR]
[TR]
[TD]709-757-2348 1-866-859-0420[/TD]
[/TR]
[TR]
[TD]Play with the best. Top name brands at Competitive Prices Gauranteed.[/TD]
[/TR]
[TR]
[TD]Music Immersion[/TD]
[/TR]
[TR]
[TD]210A Minerals Rd, Conception Bay South, NL A1W 5A1 Get directions[/TD]
[/TR]
[TR]
[TD]709-834-1895[/TD]
[/TR]
[TR]
[TD]Feener's Rock Shop[/TD]
[/TR]
[TR]
[TD]3C 9 High St, Grand Falls-Windsor, NL A2A 1C3 Get directions[/TD]
[/TR]
[TR]
[TD]709-393-1726[/TD]
[/TR]
[TR]
[TD]Your one-stop-shop for instruments, accessories and more.[/TD]
[/TR]
[TR]
[TD]Early Achievers Montessori School & Educational Centre[/TD]
[/TR]
[TR]
[TD]435 Hebron Way, St John's, NL A1A 0A1 Get directions[/TD]
[/TR]
[TR]
[TD]709-579-7323

After I entered the code, I did select the above necessary data and when I clicked the command button. I didn't get neither a new sheet nor the transposed data. I also have the design mode on.


[/TD]
[/TR]
</tbody>[/TABLE]

 

[AlphaFrog]

I also have the design mode on.

The command button doesn't "Click" with the design mode On. Turn it off.

 

[rabindra]

Dear AlphaFrog,

When I click on the command button, this is what's in the fx =EMBED("Forms.CommandButton.1","") You would probably figure out what I am doing wrong from there itself.

I've tried the code with a .xlsx & .xlsm and with the design mode off too.

If it's possible could you email me the READY FILE at rabin_08@hotmail.com with such datas along with the command button embedded so that I can simply copy my datas, paste onto it and live happily ever after

Maybe I am asking too much but I would really appreciate your help.

Looking forward to it.

Thanks a million.

The command button doesn't "Click" with the design mode On. Turn it off.

 

[AlphaFrog]

When I click on the command button, this is what's in the fx =EMBED("Forms.CommandButton.1","") You would probably figure out what I am doing wrong from there itself.

If you see =EMBED("Forms.CommandButton.1","") in the formula bar when you click the command button, the design mode is on. It must be off.

• Click the Developer tab;
• Go to the Controls group;
• Then you will view the
  
  Design Mode button there.

 

[rabindra]

Finally Finally Finally I got it

You are a life saver, AlphaFrog.

However, 1 more favor. Currently when I click the command button the transposed data adds a new sheet every time. What I want to do is,
1. Let the command button add a new sheet the very first time (eg. Sheet 2)
2. Then maybe a second command button or the same (I don't know), which will transpose the new selected range from the same file/column to Sheet 2 again but below the previous one. And so on....

I hope I am making sense

Thank a million in advance.

If you see =EMBED("Forms.CommandButton.1","") in the formula bar when you click the command button, the design mode is on. It must be off.

• Click the Developer tab;
• Go to the Controls group;
• Then you will view the
  
  Design Mode button there.

 

[AlphaFrog]

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]