macro method of range class failed error

[dragonboater]

Hi,
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). Does anyone have a workaround for this? I am a VB idiot. Here
is the macro I recorded. Thanks for any help.

Sub pasteValuesTranspose()
'
' pasteValuesTranspose Macro
' Macro recorded 3/4/2006 by Shawn Westaway
'

'
Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone,
SkipBlanks:= _
False, Transpose:=True
End Sub

 

[Bob Greenblatt]

Hi,
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). Does anyone have a workaround for this? I am a VB idiot. Here
is the macro I recorded. Thanks for any help.

Sub pasteValuesTranspose()
'
' pasteValuesTranspose Macro
' Macro recorded 3/4/2006 by Shawn Westaway
'

'
Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone,
SkipBlanks:= _
False, Transpose:=True
End Sub

My guess is that you do have not properly qualified what the selection
refers to. Have you indeed copied something before attempting to execute the
macro? In most cases, the macro should also include the copy operation.
Something like:

Workbooks("abc").sheets("def").range("a1:z1").copy
Workbooks("zyx").sheets("wvu").range("a1").pastespecial paste:=xlvalues....

 

[dragonboater]

Thanks. Yes I have copied something always before trying to execute.
But I need the macro to work with anything I copy, from anywhere, any
number of cells pasting into any number of cells. So I can't really
specify a copy operation, can I? Can I use wildcards or something?
Thx for your help
dragonboater

 

[JE McGimpsey]

Thanks. Yes I have copied something always before trying to execute.
But I need the macro to work with anything I copy, from anywhere, any
number of cells pasting into any number of cells. So I can't really
specify a copy operation, can I? Can I use wildcards or something?

One way:

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

Note that this could use some more error checking - i.e., for attempts
to paste into the same range the copy came from...

 

[dragonboater]

Hello. Well, I'm very much a VBA newbie. I tried copying and pasting
this into a macro and it gave me a syntax error for the first
statement, const, then when I removed that line, it gave me a syntax
error for the third line down, Dim rTo, etc. I don't know anything
about formatting and syntax, nor where to find out what's wrong with
the above code. Any help you can provide is appreciated.
Thanks,
Dbter