Macro to paste values transpose

[cougar77]

I used to use Excel 97 and remember this working. I copied a range & put the cursor where I wanted it to go, recorded a macro to simply paste special (values/transpose), and everything was fine. Now I have just tried it in Excell 2000 and I am getting a "PasteSpecial Method of Range class failed". Does anyone know what's up? an easy way to do this? It seems ok if you let it copy first, but I want to be able to copy/paste any length of data. I know this worked in 97. Any ideas? Thanks!

 

[gnaga]

<pre>Range("A2:A5").Copy
Range("C3").PasteSpecial Paste:=xlPasteAll, Operation:=xlNone, SkipBlanks:= _
False, Transpose:=True
</pre

Try this.

 

[cougar77]

I see what you're saying, but I need it to work for any range I've already copied, though. Like maybe a1-d1, then next time b5-b9, etc... It should be something like

Selection.PasteSpecial Paste:=xlValues, Transpose:=True

But it doesn't like that. Like I said, it was fine in 97, but it wants a range given to it ahead of time, which I want to select/copy BEFORE I run the macro.

Thanks

 

[gnaga]

<pre>Range("A1:A3").Select
Selection.Copy
Range("E9").Select
Selection.PasteSpecial Paste:=xlPasteAll, Operation:=xlNone, SkipBlanks:= _
False, Transpose:=True
Range("B1:B3").Select
Selection.Copy
Range("E11").Select
Selection.PasteSpecial Paste:=xlPasteAll, Operation:=xlNone, SkipBlanks:= _
False, Transpose:=True
Range("H16").Select
</pre>

Is this what you are looking for?

 

[cougar77]

Not really. That macro copies a specific set of cells and pastes them to s specific place. I want to be able to select whatever data I wish no matter what the size, copy it, then highlight the cell in which I want it to go & push a button that will run a macro to past the values transposed. I know this worked easily in 97...can't figure this out yet. It seems to run ok when you give it a specific range, but I don't want to do that - I want to be able to use this for many different things, not one case.

 

[Legacy 11273]

Sub Paste_ValuesOnly_Transpose()
Selection.PasteSpecial Paste:=xlValues, Transpose:=True
Application.CutCopyMode = False
End Sub

 

[cougar77]

Are you using 97 or 2000? I just tried that & received the "PasteSpecial method of range class failed" error again. hmmm...maybe I am missing something installation-wise...

 

[Legacy 11273]

The destination cell (or cells) that you select have to be a valid selection for PasteSpecial/Transpose.

Try it manually. If you are selecting an invalid destination cell, you will get an error message whether doing it manually or with a macro.

Also, the error message "PasteSpecial method of range class failed" could well mean that there is nothing in the clipboard to paste.
Are you copying the source range and then selecting the destination cell before running the macro?
This message was edited by Bali on 2002-10-15 11:57

 

[cougar77]

First of all I'm doing it on a blank page, so it's not an issue of not having room - it works manually. As far as the clipboard, I think that may be the issue. With 97 it had the basic, but with 2000 being able to hold multiple things on the clipboard may be a problem. I am copying the cells, then selecting the cell where it goes, then running the macro. This is where it fails. I found on the Microsoft help site a similar error: same name but it says you get it for having
Selection.PasteSpecial Paste:=xlColumnWidths
in the macro, to fix, you can:

WORKAROUND
To work around this problem use the number 8 in place of the xlColumnWidths value. The number 8 represents the Column Widths paste constant which is part of Microsoft Excel. The following example illustrates this:

Change this code

Selection.PasteSpecial Paste:=xlColumnWidths

to this:

Selection.PasteSpecial Paste:=8

Not sure if there is a similar fix for this...

 

[Legacy 11273]

I can't reproduce your error (using 2000).