Runtime error 1004 help

[davejago]

I am a beginner to macros and looking for help with a Runtime 1004 error I am getting. I have inherited a macro that does not appear to be working.

The macro should run so that when a button is pressed, excel goes and opens another workbook and copies some data over. However, I get the error that it cannot find the workbook. The macro file and the file I need to copy data from are in the same folder.

The macro is broken at the following line:

Sub Run_It()

'Copy1
Workbooks.Open (MyFileName) *THIS LINE IS BROKEN*
Windows(MyFileName).Activate
Sheets(SheetName).Select

(MyFileName) corresponds exactly to the file I need it to open, so that does not appear to be the problem. Both the macro file and the file I need to copy data from are in the same folder. I also tried to have the source file open at the same time, and it still does not work

Any thoughts?

 

[Big Monkey]

Try adding these 2 lines before the workbook.open command line:


ChDrive Thisworkbook.Path
ChDir Thisworkbook.Path

 

[Norie]

You say that MyFileName corresponds to the file but I don't actually see it getting a value anywhere.

Also when referring to a workbook using Windows you wouldn't use the path and the filename, just the filename.