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

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

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

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

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

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