Did you really want to copy and paste or did you just need the value? If you just need the value from a closed workbook, you can do it with:
[VBA]Sub t()
MsgBox GetValue("x:\test", "test.xlsx", "Sheet1", "A1")
End Sub

'=GetValue("c:\files", "budget.xls", "Sheet1", "A1")
Private Function GetValue(path, file, sheet, ref)
' path = "d:\files"
' file = "budget.xls"
' sheet = "Sheet1"
' ref = "A1:R30"

Dim arg As String

If Right(path, 1) <> "\" Then path = path & "\"

If Dir(path & file) = "" Then
GetValue = "file not found"
Exit Function
End If

arg = "'" & path & "[" & file & "]" & sheet & "'!" & _
Range(ref).Range("a1").Address(, , xlR1C1)

GetValue = ExecuteExcel4Macro(arg)
End Function[/VBA]

You can also use this sort of method. After a formula is entered and you are done, get the range then write it back as a Value. You can use a scratchsheet to get the parts needed.
[VBA]Sheet2.Range("A1:H8") = "= 'C:\My Documents\OzGrid\" & "[Book1.xls]Sheet2'!RC"[/VBA]