Solved: Open File on shared network drive and copy information to sheet

I have a small module that opens a file on a shared network drive and copys information back to the sheet. This works with some users but some get a runtime error#9, subscript out of range when the "Windows("TADS 1.2.xls").Activate line is ran in the code below.
I know I am missing something but I am new to this.Thanks in advance on any help you could provide.

I am using Excel 2003 sp2 and the workbook with the code is TADS 1.2.xls
Thanks....


Workbooks.Open filename:= _
"I:\BDMSFA\BDMdatafiles\tombstonecrm.xls"
Cells.Select
Selection.AutoFilter
Selection.AutoFilter Field:=16, Criteria1:=terrnum
rows("2:4000").Select
Selection.copy
Windows("TADS 1.2.xls").Activate
Range("A2").Select

Are you sure that file is open for those users?

Yes because that is where the code exists and is called from. only certain users get it and some it goes away by itself. ???
Am I refering to the object correctly or should I be using the workbook object instead??
It has been 10 years since I have coded anything and I am very rusty. Thank god for macro recorder. LOL
Thanks....

I suspect this is caused by a windows setting: hide extension of known filetypes. ANyway, you can prevent it by replacing your code with:

Code:

---

Workbooks.Open filename:= _
"I:\BDMSFA\BDMdatafiles\tombstonecrm.xls"
Cells.AutoFilter Field:=16, Criteria1:=terrnum
rows("2:4000").copy
ThisWorkbook.Activate
Range("A2").Select

---

Thanks Jan, it works fine now. :friends: