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

[slang]

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

[Jan Karel Pieterse]

Are you sure that file is open for those users?

[slang]

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....

[Jan Karel Pieterse]

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

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

[slang]

Thanks Jan, it works fine now.