Solved: Subsciprt out of Range Error with Mulitple Workbooks

[Ischyros]

I have a shared workbook that is on a shared drive at work. This database runs fine for most people except one. This user recieves "subscript out of range run time error 9" with two sub routines. The difference between these subroutines and the others is they involve another workbook. Specifically, the sub routine below always gives the error 9.
I did some investigative work and found that the erros in both subroutines happen whenever I reference the non active workbook. Lines like "Workbooks("Not Active").Range("A1") = Workbooks("Active").Range("A1")" and "Workbooks("Not Active").Activate", error out.
Is there some weird property on his computer and/or his version of excel? I am missing something!

[VBA]Sub Save_Project_List()
Dim Uname As String
Uname = Environ("UserName")
Workbooks("Project Resource Management Form").Activate
Sheets("Project List").Copy
Windows(ActiveWorkbook.Name).Activate
ActiveSheet.Unprotect
ActiveSheet.Shapes("Button 10").Delete
ActiveSheet.Shapes("Button 4").Delete
ActiveSheet.Shapes("Button 6").Delete
ActiveSheet.Shapes("Button 5").Delete
ActiveSheet.Shapes("Button 8").Delete
ActiveSheet.Shapes("Button 7").Delete
ActiveSheet.Shapes("Button 9").Delete
ActiveSheet.Shapes("Button 11").Delete
ActiveSheet.Shapes("CommandButton2").Delete
ActiveSheet.Shapes("CommandButton1").Delete
ActiveSheet.Shapes("CommandButton3").Delete
ActiveSheet.Shapes("Button 12").Delete

ActiveWorkbook.SaveAs FileName:= _
"C:\Documents and Settings\" + Uname + "\Desktop\Copy - Project List.xls", _
FileFormat:=xlNormal, Password:="", WriteResPassword:="", _
ReadOnlyRecommended:=False, CreateBackup:=False

End Sub
[/VBA]

[lucas]

Subscript out of range usually means it cant find whatever it is looking for.

Are you sure the workbook is open since you are using activate?

[Kenneth Hobs]

You can use this to check that a workbook is open.
[VBA]Function IsWorkbookOpen(stName As String) As Boolean
Dim Wkb As Workbook
On Error Resume Next ' In Case it isn't Open
Set Wkb = Workbooks(stName)
If Not Wkb Is Nothing Then IsWorkbookOpen = True
'Boolean Function assumed To be False unless Set To True
End Function[/VBA]

Here are a couple of desktop path methods. Your method should work most of the time and would not appear to be the problem now.
[VBA]Sub TestDeskTopPaths()
MsgBox DesktopFolder, , "DeskTopFolder()"
MsgBox DesktopPath, , "DesktopPath()"
End Sub

Function DesktopFolder() As String
DesktopFolder = CreateObject("WScript.Shell").SpecialFolders("Desktop") & Application.PathSeparator
End Function

Function DesktopPath() As String
Dim wsh As Object, dPath As String
Set wsh = CreateObject("Wscript.Shell")
dPath = wsh.SpecialFolders("Desktop") & Application.PathSeparator
Set wsh = Nothing
DesktopPath = dPath
End Function
[/VBA]

[Ischyros]

Thanks Kenneth.....I discovered the problem.

That user has a windows setting such that the workbook isn't

Workbook("Book1") but Workbook("Book1.xls").

I just wrote some code to check if an extension exists and if so, calls the workbook(s) appropriatley.