|While it's true that you cannot copy and paste from a closed workbook, you can link to it, pull in the data and then Copy/PasteSpecial the values.|
Just as a basic example, this will essentially "copy" the data from A1:J10 of a closed workbook into A1:J10 of the workbook where the macro is run by creating formulae to link to the closed workbook and then eliminating the formulae to leave just the data.
'Fill A1:J10 with Link Formulae
For myRw = 1 To 10
For myCol = 1 To 10
Cells(myRw, myCol).FormulaR1C1 = _
"='C:\[Book1.xls]Sheet1'!R" & myRow & "C" & myCol
With Range(Cells(1, 1), Cells(10, 10))
Obviously, if you need to deal with formatting and other "stuff", then the code would need to be more complex.