Macro for paste values and transpose

[straggleyway]

Hi all,

This is driving me crazy. Really grateful for help. I want to make a macro that I can copy a column of numbers, select a cell... then run the macro - which will transpose them and paste the values.


I've tried to make this by Recorder... but I keep getting an error.

Run-time error 1004
Paste-Special method of Range class failed.

I've seen other people in the past post this questions, but it seems noone has solved it!

Any ideas? Code below....

Thank you!!


Sub Transpose()
'
' Transpose Macro
' Macro recorded 11/03/2009
'
'
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=True
End Sub

 

[Norie]

You can't transpose a selection where the destination range will include anything in the orignal source range.

Try this in Excel.

1 Enter some data in column A.

2 Select it.

3 Goto Edit>Paste special and click Transpose and then OK.

You'll get an error.

 

[Datsmart]

If you just want to Transpose what you have in the clipboard to the active cell, this will do that:

Sub Transpose()
    ActiveCell.PasteSpecial Paste:=xlPasteValues, Transpose:=True
End Sub