Paste Special error in a Macro

[DontKnow]

Hi Gys,

I am playing with a macro and I am getting an error message and I have tried
everything to fix it but have no idea how to fix it!!

The error that I receive is:

"Paste special method of Range class failed"


I get this error at the Paste Special Line.
"Selection.PasteSpecial Paste:=xlPasteAll,
Operation:=xlPasteSpecialOperationNone, SkipBlanks:= _
False, Transpose:=False"

Can anyone help me.

What I am trying to do is to copy a row from 1 spreadsheet to another
spreadsheet using the paste special method. I just keep getting an error!!

Damm thing!

Cheers,

Thanks for you help!!


My code is:

Dim myrange As Range
Dim distination As Range

Set myrange = Application.InputBox("Select cells...", , , , , , , 8)

Range(myrange.Address(False, False)).Select


'Range("A10:AG10").Select this is the
original line


Selection.Copy
Windows("Master.xls").Activate


Set distination = Application.InputBox("Select cells...", , , , , , , 8)


Range(distination.Address(False, False)).Select


'Range("A11").Select this is the original line

Selection.PasteSpecial Paste:=xlPasteAll,
Operation:=xlPasteSpecialOperationNone, SkipBlanks:= _
False, Transpose:=False

ActiveSheet.Paste Link:=True

Range("A12").Select

End Sub

 

[Dave Peterson]

There are somethings that can kill the cutcopy clipboard. I don't see anything
in your code that does it, but maybe there's an event that's firing when you
select something.

I'd try:

Dim myrange As Range
Dim distination As Range

set myrange = nothing
on error resume next
Set myrange = Application.InputBox("Select cells...", , , , , , , 8)
on error goto 0

if myrange is nothing then
exit sub 'user hit cancel
end if

Workbooks("Master.xls").Activate

set distination = nothing
on error resume next
Set distination = Application.InputBox("Select cells...", , , , , , , 8)
on error goto 0

if distination is nothing then
exit sub 'user hit cancel
end if

myrng.copy
distination.PasteSpecial Paste:=xlPasteAll, _
Operation:=xlPasteSpecialOperationNone, _
SkipBlanks:=False, Transpose:=False

myrng.copy 'may not be necessary, but won't hurt
application.goto reference:=distination
ActiveSheet.Paste Link:=True

(untested, uncompiled.)

 

[DontKnow]

Thanks Dave,

Worked well!!

What code would I need to add to have the macro open the master worksheet
automatically if I did not already have it open??

 

[Dave Peterson]

dim MstrWkbk as workbook
dim MstrWkbkName as string
dim MstrWkbkFldr as string

mstrwkbkname = "Master.xls"
mstrwkbkfldr = "C:\somefolder\here\"
if right(mstrwkbkfldr,1) <> "\" then
mstwkbkfldr = mstrwkbkfldr & "\"
end if

set mstrwkbk = nothing
on error resume next
set mstrwkbk = workbooks(mstrwkbkname) 'no folder here
on error goto 0

if mstrwkbk is nothing then
'it's not open
on error resume next
'folder and filename here
set mstrwkbk = workbooks.open(filename:=mstrwkbkfldr & mstrwkbkname)
on error goto 0
if mstrwkbk is nothing then
msgbox "it wasn't open and it couldn't be found!"
exit sub '???
end if
end if

(untested, uncompiled. Watch for typos.)