Subscript out of Range, Error 9 - MS Excel

[jdegrave]

This one really boggles my mind as it works fine on my machine but no-one else can seem to run it. When they do they get a Subscript out of Range 'Error 9' message.

Sub Save()
'On Error GoTo Auto_Error
Application.ScreenUpdating = False
' Defines variable names and types
Dim Sname, Tempname, Wbmaster, WBclient As Variant, i As Integer
Tempname = ActiveWorkbook.Name
i = Len(Tempname) - 4
Wbmaster = Left(Tempname, i)
' Opens dialog box asking the user for the name of the file and the location to save it at
Sname = Application.GetSaveAsFilename(filefilter:="Excel Workbooks (*.xls),*.xls", _
title:="Director's Report - Save Client Copy&quot
If Sname <> False Then
' Adds new workbook - saves as the name defined by the user
Workbooks.Add
ActiveWorkbook.SaveAs Sname
' Temporarily holds just the name of the new workbook
Tempname = ActiveWorkbook.Name
' Strips out the file extension
i = Len(Tempname) - 4
WBclient = Left(Tempname, i)
' Copies data from the master file to the client file
Workbooks(Wbmaster).Activate
Sheets(&quot;Weekly&quot.Select
Cells.Select
Selection.Copy
Workbooks(WBclient).Activate
ActiveWorkbook.Sheets(&quot;Sheet1&quot.Range(&quot;A1&quot.Select
Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, _
skipblanks:=False, Transpose:=False
Selection.PasteSpecial Paste:=xlFormats
Application.CutCopyMode = False
Range(&quot;A1&quot.Select
ActiveWindow.Zoom = 90
Workbooks(Wbmaster).Activate
Range(&quot;A1&quot.Select
ActiveWorkbook.Sheets(&quot;Sec6 Definitions&quot.Activate
Cells.Select
Selection.Copy
Workbooks(WBclient).Activate
Sheets(&quot;Sheet2&quot.Select
Range(&quot;A1&quot.Select
Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, _
skipblanks:=False, Transpose:=False
Selection.PasteSpecial Paste:=xlFormats
Application.CutCopyMode = False
Range(&quot;A1&quot.Select
ActiveWindow.Zoom = 75
Workbooks(Wbmaster).Activate
Range(&quot;A1&quot.Select
ActiveWorkbook.Sheets(&quot;Monthly&quot.Select
Range(&quot;A1&quot.Select
Cells.Copy
Workbooks(WBclient).Activate
Sheets(&quot;Sheet3&quot.Select
Range(&quot;A1&quot.Select
Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, _
skipblanks:=False, Transpose:=False
Selection.PasteSpecial Paste:=xlFormats
Application.CutCopyMode = False
Range(&quot;A1&quot.Select
ActiveWindow.Zoom = 85
Workbooks(Wbmaster).Activate
Range(&quot;A1&quot.Select
' Renames worksheets in the new workbook
Workbooks(WBclient).Activate
Sheets(&quot;Sheet1&quot.Name = &quot;Weekly&quot;
Sheets(&quot;Sheet2&quot.Name = &quot;Sec6 Definitions&quot;
Sheets(&quot;Sheet3&quot.Name = &quot;Monthly&quot;
Sheets(&quot;Weekly&quot.Select
ActiveWorkbook.Save
ActiveWorkbook.Close
Workbooks(Wbmaster).Activate
Sheets(&quot;Weekly&quot.Select
Range(&quot;A1&quot.Select
End If
Application.ScreenUpdating = True
' Exit Sub
'Auto_Error:
' Dim errmsg, style, title, response As String
' errmsg = &quot;File not Saved!&quot;
' style = vbExclamation
' title = &quot;Director 's Report&quot;
' response = MsgBox(errmsg, style, title)
' Exit Sub
End Sub

I've Rem'd out the error trapping routine so I could see what the exact error is. What bother's me most is I con't reproduce the error on my own machine. Weird...

 

[xlbo]

Where is the error occuring...which line ?? Rgds
~Geoff~

 

[sfvb]

I think you are getting the subscript out of range message when switching between WBclient and WBmaster. If you leave the file extion of &quot;.xls&quot; in the workbook names, then the error should disappear.

 

[ttmac]

The reason may be because you are trying to acivate a work book &quot;workboook&quot; when the workbook is called &quot;workbook.xls&quot;

Tempname = ActiveWorkbook.Name
i = Len(Tempname) - 4
Wbmaster = Tempname
Workbooks(Wbmaster).Activate

 

[jdegrave]

xlbo

The error occurs here:

WBclient = Left(Tempname, i)
' Copies data from the master file to the client file
--==> Workbooks(Wbmaster).Activate

What I find odd is when I run this code on my own machine, it's fine. I've e-mailed it to other people on our project and they get the 'Subscript out of Range Error'. I'm going to try sfvb's suggestion now to see if that solves the problem....

 

[jdegrave]

I just tried your suggestion of leaving the file extension in and that worked. Thank you very much guys. I've updated the code to eliminate the lines where the file extension is removed also for &quot;cleanliness&quot;. I'm still stumped as to why it would run fine on my machine. Oh well...