run-time error '9' subscript out of range 2007 excel

[rulo777]

Hi I am using Microsoft Excel 2007.

I get the 'Run Time Error '9' Subscript out of range' when running the macro that I use everyday. This macro formats a report I create everyday and it is available for other users so the macro is on a shared drive and everyone can use it. The weird thing is that I wasthe only one receiving this messsage. if I run the macro from other computer it was working smoothly. I was only experiencing this problem on this specific work station. I was running it from other stations but now it gives me the same error on every computer.



If I click Debug it highlights the following line in my macro:



Windows(MyFile).Activate


Help would be greatly appreciated!

Thanks



I am copying the code of that module:

Sub OpenFile()
 '
 ' OpenFile Macro
 '
 Dim MyFile As String
 '
     MyFile = Range("J3")
 '
     Workbooks.Open Filename:= _
         MyFile
     Sheets("MATERIAL SHORTAGE REPORT").Select
     Range("A1").Select
     Sheets.Add
     Sheets.Add
     Sheets.Add
     Sheets("Sheet1").Select
     Sheets("Sheet1").Name = "MASTER"
     Sheets("Sheet2").Select
     Sheets("Sheet2").Name = "CAN'T RELEASE"
     Sheets("Sheet3").Select
     Sheets("Sheet3").Name = "RELEASED JOB SHORTAGES"
     Sheets("MATERIAL SHORTAGE REPORT").Select
     Range("A1").Select
     Sheets("MASTER").Select
     Sheets("MASTER").Move Before:=Sheets(5)
     Sheets("CAN'T RELEASE").Select
     Sheets("CAN'T RELEASE").Move Before:=Sheets(5)
     Sheets("RELEASED JOB SHORTAGES").Select
     Sheets("RELEASED JOB SHORTAGES").Move Before:=Sheets(5)
     Sheets("MATERIAL SHORTAGE REPORT").Select
     Range("A1").Select
     Windows("ShortageReportMacro.xlsm").Activate
     Sheets("Home").Select
     Range("A12").Select
     Windows(MyFile).Activate
     Sheets("MATERIAL SHORTAGE REPORT").Select
     Range("A1").Select
     Cells.Select
     Selection.Copy
     Sheets("MASTER").Select
     ActiveSheet.Paste
     Range("A1").Select
     Windows("ShortageReportMacro.xlsm").Activate
     Sheets("FileHeader").Select
     Range("A1").Select
     Range(Selection, Selection.End(xlToRight)).Select
     Application.CutCopyMode = False
     Selection.Copy
     Windows(MyFile).Activate
     Sheets("MASTER").Select
     Range("A1").Select
     ActiveSheet.Paste
     Range("A2").Select
     ActiveWindow.FreezePanes = True
     Columns("A:A").Select
     Selection.Delete Shift:=xlToLeft
     Columns("B:B").Select
     Selection.EntireColumn.Hidden = True
     Columns("C:D").Select
     Columns("C:D").EntireColumn.AutoFit
     Columns("E:E").Select
     Selection.EntireColumn.Hidden = True
     Columns("F:G").Select
     Columns("F:G").EntireColumn.AutoFit
     Columns("H:H").Select
     Selection.EntireColumn.Hidden = True
     Columns("I:N").Select
     Columns("I:N").EntireColumn.AutoFit
     Columns("O:O").Select
     Selection.EntireColumn.Hidden = True
     Columns("R:R").Select
     Selection.EntireColumn.Hidden = True
     Columns("X:X").Select
     Selection.Cut
     Columns("P:P").Select
     Selection.Insert Shift:=xlToRight
     Columns("Z:AA").Select
     Columns("Z:AA").EntireColumn.AutoFit
     Selection.Cut
     Columns("T:T").Select
     Selection.Insert Shift:=xlToRight
     Columns("AD:AD").Select
     Selection.Cut
     Columns("Y:Y").Select
     Selection.Insert Shift:=xlToRight
     Columns("AB:AB").Select
     Selection.Cut
     Columns("Z:Z").Select
     Selection.Insert Shift:=xlToRight
     Columns("F:F").Select
     With Selection.Font
         .Color = -4165632
         .TintAndShade = 0
     End With
     Selection.Font.Bold = False
     Selection.Font.Bold = True
     Columns("Q:Q").Select
     With Selection.Font
         .Color = -4165632
         .TintAndShade = 0
     End With
     Selection.Font.Bold = False
     Selection.Font.Bold = True
     Columns("U:U").Select
     With Selection.Font
         .Color = -4165632
         .TintAndShade = 0
     End With
     Selection.Font.Bold = False
     Selection.Font.Bold = True
     Columns("I:I").Select
     With Selection.Font
         .Color = -11489280
         .TintAndShade = 0
     End With
     Selection.Font.Bold = False
     Selection.Font.Bold = True
     Columns("P:P").Select
     With Selection.Font
         .Color = -16776961
         .TintAndShade = 0
     End With
     Selection.Font.Bold = False
     Selection.Font.Bold = True
     Columns("W:W").Select
     With Selection.Font
         .Color = -16776961
         .TintAndShade = 0
     End With
     Selection.Font.Bold = False
     Selection.Font.Bold = True
     Columns("Q:W").Select
     Columns("Q:W").EntireColumn.AutoFit
     Range("A1").Select
     ActiveWorkbook.Worksheets("MASTER").Sort.SortFields.Clear
     ActiveWorkbook.Worksheets("MASTER").Sort.SortFields.Add Key:=Range("K:K" _
         ), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
     With ActiveWorkbook.Worksheets("MASTER").Sort
         .SetRange Range("A:AE")
         .Header = xlYes
         .MatchCase = False
         .Orientation = xlTopToBottom
         .SortMethod = xlPinYin
         .Apply
     End With
 ' adding
     Range("A1").Select
     Selection.EntireColumn.Insert
     Selection.EntireColumn.Insert
     Selection.EntireColumn.Insert
     Selection.EntireColumn.Insert
     Selection.EntireColumn.Insert
     Selection.EntireColumn.Insert
     Windows("ShortageReportMacro.xlsm").Activate
     Sheets("FileHeader").Select
     Range("A5:F6").Select
     Selection.Copy
     Windows(MyFile).Activate
     Range("A1").Select
     ActiveSheet.Paste
     Application.CutCopyMode = False
     Range("A2:F2").Select
    Selection.AutoFill Destination:=Range("A2:F9000")
     Columns("A:F").Select
     Columns("A:F").EntireColumn.AutoFit
     Range("E1").Select
     Selection.End(xlDown).Select
     Selection.Offset(1, 0).Select
     Selection.End(xlToLeft).Select
     Selection.End(xlToLeft).Select
     Range(Selection, Selection.End(xlToRight)).Select
     Range(Selection, Selection.End(xlDown)).Select
     Selection.ClearContents
     Range("A1").Select
 ' adding data to each tab
     Cells.Select
     Selection.Copy
     Sheets("CAN'T RELEASE").Select
     ActiveSheet.Paste
     Sheets("RELEASED JOB SHORTAGES").Select
     ActiveSheet.Paste
     Sheets("CAN'T RELEASE").Select
     Range("A1").Select
     Application.CutCopyMode = False
     Range("A1").Select
 ' adding in sort and subtotals for Can't release tab
 '    Sheets("CAN'T RELEASE").Select
 '    Range("E2").Select
 '    ActiveWorkbook.Worksheets("CAN'T RELEASE").Sort.SortFields.Clear
 '    ActiveWorkbook.Worksheets("CAN'T RELEASE").Sort.SortFields.Add Key:=Range( _
         "M2:M10000"), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:= _
         xlSortNormal
 '    ActiveWorkbook.Worksheets("CAN'T RELEASE").Sort.SortFields.Add Key:=Range( _
         "O2:O10000"), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:= _
         xlSortNormal
 '    ActiveWorkbook.Worksheets("CAN'T RELEASE").Sort.SortFields.Add Key:=Range( _
         "E2:E10000"), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:= _
         xlSortNormal
  '   With ActiveWorkbook.Worksheets("CAN'T RELEASE").Sort
  '       .SetRange Range("A1:AH10000")
  '       .Header = xlYes
  '       .MatchCase = False
  '       .Orientation = xlTopToBottom
  '       .SortMethod = xlPinYin
  '       .Apply
  '   End With
  '   Application.Calculation = xlManual
  '   Columns("O:O").Select
  '   Selection.Subtotal GroupBy:=1, Function:=xlCount, TotalList:=Array(1), _
         Replace:=True, PageBreaks:=False, SummaryBelowData:=True
  '   Selection.Delete Shift:=xlToLeft
  '   Range("E2").Select
  '   Application.Calculation = xlAutomatic
  '   Windows("ShortageReportMacro.xlsm").Activate
  '   Range("E1").Select
   Windows("ShortageReportMacro.xlsm").Activate
     Sheets("Home").Select
     Range("A1").Select
 
' next portion
 

End Sub