Copy from a closed workbook to an open workbook

Question

I'm trying to create code to copy from an unopened excel workbook to an open book.

This is the code I've been using:

Sub foo()
Dim x As Workbook
Dim y As Workbook

Set x = Workbooks.Open("R:\Manufacturing\First Off Log\First Off Log.xlsm", ReadOnly:=True)
Set y = Workbooks.Open("R:\Manufacturing\First Off Log\Analysis\First Off Log Analysis.xlsm")

x.Sheets("Sheet1").Range("A:K").Copy

y.Sheets("Data Input").Range("A:K").PasteSpecial

'Close x:
x.Close

End Sub

'First Off Log Analysis' will already be open. The code above reopens the workbook and causes it to crash!

Any help would be really appreciated!

Thank you! :-)

Answer 1

Concerning that First Off Log.xlsm is the workbook where the code is placed, it is opened already. Thus instead of:

Set x = Workbooks.Open("R:\Manufacturing\First Off Log\First Off Log.xlsm", ReadOnly:=True)

write

Set x = ThisWorkbook