Error on copy from one workbook to another

[Milan Cetic]

I'm trying to copy cells from one workbook to another (copyfrom.xslm and copyto.xlsx)
This is the code I'm using

Sub CopyToWorkbook()

Range("A1:D4").Copy Workbooks("copyto").Worksheets("Sheet1").Range("A5")

End Sub

When I execute the code I get the Runtime error '9': Subscript out of range.
It's like there is no copyto file although it is open. Makes no sense but this is what's happening. Any ideas?

Thanks!

 

[Fluff]

Hi & welcome to MrExcel.
How about

Sub CopyToWorkbook()

Workbooks("copyfrom.xlsm").Sheets("Sheet1").Range("A1:D4").Copy Workbooks("copyto.xlsx").Worksheets("Sheet1").Range("A5")

End Sub

 

[Milan Cetic]

Hi & welcome to MrExcel.
How about

Sub CopyToWorkbook()

Workbooks("copyfrom.xlsm").Sheets("Sheet1").Range("A1:D4").Copy Workbooks("copyto.xlsx").Worksheets("Sheet1").Range("A5")

End Sub

Tried that as well, same result.

 

[Milan Cetic]

Tried that as well, same result.

I tried even with full path C:\..\copyfrom.xslm

 

[Fluff]

Oops, it should have been

Sub CopyToWorkbook()

Workbooks("copyfrom.xlsm").Sheets("Test1").Range("A1:D4").Copy Workbooks("copyto.xlsx").Worksheets("Sheet1").Range("A5")

End Sub

 

[Milan Cetic]

Tried that and it's not working.
I have even tried this:
Workbooks("copyto").Worksheets("Sheet1").Range("A1").Value = 1 (also with extension and full path)

End I get the same error message. It looks like VBA can't "see" any other file. I have also checked that all files have "Full control" permissions

 

[Fluff]

Are both workbooks open, when you run the code?
Check that the sheet names don't have any leading/trailing spaces.

 

[Milan Cetic]

Check and check. Both workbooks open. No trailing/leading spaces. Copied full path from File explorer.

 

[Fluff]

Do not use the full path, if the workbook & sheet names are as per you image, just use the code from post#5

 

[Milan Cetic]

Tried it with just the file name, same result. Error all the time.