Run time 9 error

Hi all,
I have been wrangling with this problem for a few months now. I have a spreadsheet that contains a couple of macros. Each of these macros i recorded in macro wizard, and essentially they go to another spreadsheet (already opened and in the same instance of excel (file>open)) grab a sheet of data and paste it back into the source. Now the problem is that they work brilliantly on my pc, but not on anyone elses.
I will attach the code below, But some things i have checked are:

1) the second file is opened in the same instance of excel using file>Open
2) we are using the same version of excel (2007)

One other possibly noteworthy point is that the files are on different network locations (i am sometimes opening them from USB, sometimes via VPN, with no issue, the others are directly on the network)

Would really appreciate any help on this, i thought i had it licked once or twice but unfortunatly it keeps coming back to haunt me, the downside being i have to do the work every month when the process is run!
Thanks again,
dave

Please Login or Register  to view this content.

PS maybe i should have mentioned that everyone else sees a Runtime 9 error!

Thanks Norie, No i havent tried that, but i will do so now! cheers

No the file is already opened, in the same instance using file>open in excel
cheers,
Dave

Hi Norie & all,
I tried to remove the .XLS extensions but no luck, i note in the debugger that its falling over on the following line

Please Login or Register  to view this content.

I have tried replacting 'workbooks' with 'Windows', again no joy...
thanks for your help guys!
dave