Coding to make a transpose values button
Hi,
Can anyone send me some code so I can attach it to a button in the tool bar that will transpose and paste values? Thanks Sally |
Coding to make a transpose values button
That's a "plain" request. So I recorded my actions and here you go...
sub Sally() Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:= _ False, Transpose:=True end sub Sally wrote: Hi, Can anyone send me some code so I can attach it to a button in the tool bar that will transpose and paste values? Thanks Sally |
Coding to make a transpose values button
Thanks for replying,
I know it sounds like a plain request but when I pasted the line of code into the VB Editor, selected data in the range A4:A11, press ctrl c selected cell D4 and ran the macro I got the following error message... Run-time erro '1004': PasteSpecial method of Range class failed I do alot of data entry work and often need to transpose values. Generally I would copy the row of data and go into paste special in the edit menu and select values and transpose. What I would like is to have a button on the tool bar so that after I have selected the data and done a control c (for copy) I can select a cell where the data needs to be input and press the button instead of having to navigate through the menus. I hope that makes sense, let me know if you need further info. Thanks for your time! jseven wrote: That's a "plain" request. So I recorded my actions and here you go... sub Sally() Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:= _ False, Transpose:=True end sub Sally wrote: Hi, Can anyone send me some code so I can attach it to a button in the tool bar that will transpose and paste values? Thanks Sally |
Coding to make a transpose values button
Hi Sally,
JSeven's code worked for me, providing I removed line wraps: '============= Public Sub Sally() Selection.PasteSpecial _ Paste:=xlValues, _ Operation:=xlNone, _ SkipBlanks:=False, _ Transpose:=True End Sub '<<============= Using your sequence: so that after I have selected the data and done a control c (for copy) I can select a cell where the data needs to be input and press the button I experienced no problems. --- Regards, Norman "Sally" wrote in message ups.com... Thanks for replying, I know it sounds like a plain request but when I pasted the line of code into the VB Editor, selected data in the range A4:A11, press ctrl c selected cell D4 and ran the macro I got the following error message... Run-time erro '1004': PasteSpecial method of Range class failed I do alot of data entry work and often need to transpose values. Generally I would copy the row of data and go into paste special in the edit menu and select values and transpose. What I would like is to have a button on the tool bar so that after I have selected the data and done a control c (for copy) I can select a cell where the data needs to be input and press the button instead of having to navigate through the menus. I hope that makes sense, let me know if you need further info. Thanks for your time! jseven wrote: That's a "plain" request. So I recorded my actions and here you go... sub Sally() Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:= _ False, Transpose:=True end sub Sally wrote: Hi, Can anyone send me some code so I can attach it to a button in the tool bar that will transpose and paste values? Thanks Sally |
Coding to make a transpose values button
Actually Sally when I wrote that ("plain request") I was surprised that
you were having difficulty with it. (Think I've seen you post on here before.) So I thought I'd just record and see what happens. I posted my code, then ran it and saw what your issue is. (sould run first before I post I guess) However the code does work. What I found was that if I higlighted a range, copied it, then tried to execute the macro i built with alt+f8 to run the macro, i lost the copy range. When I attatched the macro to the a custom toolbar button, it worked fine. I guess the macro run dialog box emptys the clipboard or something. HTH Jamie Norman Jones wrote: Hi Sally, JSeven's code worked for me, providing I removed line wraps: '============= Public Sub Sally() Selection.PasteSpecial _ Paste:=xlValues, _ Operation:=xlNone, _ SkipBlanks:=False, _ Transpose:=True End Sub '<<============= Using your sequence: so that after I have selected the data and done a control c (for copy) I can select a cell where the data needs to be input and press the button I experienced no problems. --- Regards, Norman "Sally" wrote in message ups.com... Thanks for replying, I know it sounds like a plain request but when I pasted the line of code into the VB Editor, selected data in the range A4:A11, press ctrl c selected cell D4 and ran the macro I got the following error message... Run-time erro '1004': PasteSpecial method of Range class failed I do alot of data entry work and often need to transpose values. Generally I would copy the row of data and go into paste special in the edit menu and select values and transpose. What I would like is to have a button on the tool bar so that after I have selected the data and done a control c (for copy) I can select a cell where the data needs to be input and press the button instead of having to navigate through the menus. I hope that makes sense, let me know if you need further info. Thanks for your time! jseven wrote: That's a "plain" request. So I recorded my actions and here you go... sub Sally() Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:= _ False, Transpose:=True end sub Sally wrote: Hi, Can anyone send me some code so I can attach it to a button in the tool bar that will transpose and paste values? Thanks Sally |
Coding to make a transpose values button
Thanks Jamie and Norman!!!
I was running the code to test prior to attaching it to a button. Once I attached it to a button and tested it worked! Much appreciated as this will save me at least 100 mouse clicks a day! jseven wrote: Actually Sally when I wrote that ("plain request") I was surprised that you were having difficulty with it. (Think I've seen you post on here before.) So I thought I'd just record and see what happens. I posted my code, then ran it and saw what your issue is. (sould run first before I post I guess) However the code does work. What I found was that if I higlighted a range, copied it, then tried to execute the macro i built with alt+f8 to run the macro, i lost the copy range. When I attatched the macro to the a custom toolbar button, it worked fine. I guess the macro run dialog box emptys the clipboard or something. HTH Jamie Norman Jones wrote: Hi Sally, JSeven's code worked for me, providing I removed line wraps: '============= Public Sub Sally() Selection.PasteSpecial _ Paste:=xlValues, _ Operation:=xlNone, _ SkipBlanks:=False, _ Transpose:=True End Sub '<<============= Using your sequence: so that after I have selected the data and done a control c (for copy) I can select a cell where the data needs to be input and press the button I experienced no problems. --- Regards, Norman "Sally" wrote in message ups.com... Thanks for replying, I know it sounds like a plain request but when I pasted the line of code into the VB Editor, selected data in the range A4:A11, press ctrl c selected cell D4 and ran the macro I got the following error message... Run-time erro '1004': PasteSpecial method of Range class failed I do alot of data entry work and often need to transpose values. Generally I would copy the row of data and go into paste special in the edit menu and select values and transpose. What I would like is to have a button on the tool bar so that after I have selected the data and done a control c (for copy) I can select a cell where the data needs to be input and press the button instead of having to navigate through the menus. I hope that makes sense, let me know if you need further info. Thanks for your time! jseven wrote: That's a "plain" request. So I recorded my actions and here you go... sub Sally() Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:= _ False, Transpose:=True end sub Sally wrote: Hi, Can anyone send me some code so I can attach it to a button in the tool bar that will transpose and paste values? Thanks Sally |
All times are GMT +1. The time now is 01:45 PM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com