PasteSpecial method of range class failed

[dragonboater]

Using Microsoft Excel X (at home) and 2004 (at work) I am trying to
copy rows of data from worksheets in one workbook to columns in
worksheets in another workbook while using the transpose and paste
values command under pastespecial. I tried to make a macro to do this
and get a PasteSpecial method of range class failed error (on both
systems). Someone earlier posted this macro for me to try, but I also
get an error using this: It is a compile error - Syntax error. I don't
know how to fill in any of the blanks, if they are blanks, or what I
need to fill in to make this work. Thanks for any help.

Public Sub CopyAndPaste()
Const sTITLE As String = "Copy and Paste with Transpose"
Dim rFrom As Range
Dim rTo As Range
On Error Resume Next
Set rFrom = Application.InputBox( _
Prompt:="Choose a range to copy from:", _
Title:=sTITLE, _
Type:=8, _
Default:=Selection.Address(False, False))
If rFrom Is Nothing Then Exit Sub 'user cancelled
Set rTo = Application.InputBox( _
Prompt:="Choose a range to copy to:", _
Title:=sTITLE, _
Type:=8, _
Default:=Selection.Address(False, False))
If rTo Is Nothing Then Exit Sub 'user cancelled
On Error GoTo 0
rFrom.Copy
rTo.PasteSpecial Paste:=xlValues, _
Operation:=xlNone, _
Skipblanks:=False, _
Transpose:=True
End Sub

 

[JE McGimpsey]

Someone earlier posted this macro for me to try, but I also get an
error using this: It is a compile error - Syntax error.

There's no syntax error in the macro as you posted it.

Depending on how you're reading this newsgroup, some readers will post
with non-breaking spaces or other non-printing characters that can give
a syntax error. Reading newsgroups via Google, this seems to happen
frequently.

Try deleting all the white space at the beginning of each line of the
macro. You can replace it with space characters or tabs to maintain
readability.