Sometimes opening or closing a workbook will clear the clipboard.

I'd change the order around and use something like:

Option Explicit
Sub Load()

Dim wkbkSource As Workbook
Dim DestCell As Range
Dim RngToCopy As Range
Dim wkbkSourceWasOpen As Boolean
Dim myPath As String
Dim myFileName As String

myPath = "H:\My Documents\TESTS\"
If Right(myPath, 1) < "\" Then
myPath = myPath & "\"
End If

myFileName = "call list.xls"

'where the paste should be
Set DestCell = ActiveCell

Set wkbkSource = Nothing
On Error Resume Next
Set wkbkSource = Workbooks(myFileName)
On Error GoTo 0

wkbkSourceWasOpen = True
If wkbkSource Is Nothing Then
'not open, so open it
wkbkSourceWasOpen = False
On Error Resume Next
Set wkbkSource = Workbooks.Open(Filename:=myPath & myFileName, _
ReadOnly:=True)
On Error GoTo 0

If wkbkSource Is Nothing Then
MsgBox "Source workbook couldn't be found!"
Exit Sub
End If
End If

Set RngToCopy = wkbkSource.Worksheets(1).Range("A2:A20")

RngToCopy.Copy
DestCell.PasteSpecial Paste:=xlPasteValues

If wkbkSourceWasOpen Then
'leave it open, do nothing
Else
wkbkSource.Close savechanges:=False
End If

End Sub

Untested, but it did compile.

Coder1215 wrote:

Hi,

I'm using XL2007 on Vista. I want to put in Personal.xlsb a code which
will copy range of data from another workbook and paste it to
activecell in any open workbook. I wrote some code but there seems to
be an error when trying to paste values("Pastespecial methow of range
class failed").

Sub Load()

Dim wbA As Workbook
Dim cellA As Range

Set wbA = Workbooks.Open(Filename:="H:\My Documents\TESTS\CALL
LIST.xls")
Windows("CALL LIST.xls").Activate
Workbooks("CALL LIST.xls").Worksheets(1).Range("A2:A20").Copy
Windows("CALL LIST.xls").Close (False)
Application.ActiveWindow.ActiveCell.Select
ActiveCell.PasteSpecial (xlPasteValues)

End Sub

Can someone advise on the solution?

thanks and regards

--

Dave Peterson