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

 

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. This macro is on a shared drive so everyone can use it. The weird thing is that I am the only one receiving this messsage. if I run the macro from other computer it works smoothly. I am only experiencing this problem on this specific work station.

 

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

,

 

Question Info


Last updated April 4, 2019 Views 1,913 Applies to:

Hi,

try this..

 

Sub OpenFile()
'
' OpenFile Macro
'
Dim MyFile As String
'
MyFile = Sheets("Data").Range("J3").Value '<<< change name

-----------------------------
Office 365 on Windows 10

Did this solve your problem?

Sorry this didn't help.

Great! Thanks for marking this as the answer.

How satisfied are you with this reply?

Thanks for your feedback, it helps us improve the site.

How satisfied are you with this response?

Thanks for your feedback.

Thank you for the answer, it did't work, it shows me the same error but earlier. I used to pull the file and give the error after pulling the file, now it gives me the error without pulling the file.

Did this solve your problem?

Sorry this didn't help.

Great! Thanks for marking this as the answer.

How satisfied are you with this reply?

Thanks for your feedback, it helps us improve the site.

How satisfied are you with this response?

Thanks for your feedback.

Any other idea? now the macro doesn't work on any station :(

Did this solve your problem?

Sorry this didn't help.

Great! Thanks for marking this as the answer.

How satisfied are you with this reply?

Thanks for your feedback, it helps us improve the site.

How satisfied are you with this response?

Thanks for your feedback.

Okay,

follow the classic way..

....................

Dim myWB As Workbook, WB As Workbook
Set myWB = ThisWorkbook '<<< the open workbook

'you want to open .xlsm or .xlsx ? change the extension

Set WB = Workbooks.Open(C:\Excel Folder\Workbook1.xlsm) '<<< change Path and WB Name

.................

 

note

don't try to do ALL IN ONE
but, step by step.

 

 

 

-----------------------------
Office 365 on Windows 10

Did this solve your problem?

Sorry this didn't help.

Great! Thanks for marking this as the answer.

How satisfied are you with this reply?

Thanks for your feedback, it helps us improve the site.

How satisfied are you with this response?

Thanks for your feedback.

I would have given much the same response as TasosK did, but you might try this variation instead, assuming that cell J3 that holds the filename is on a sheet named Data, change as required.  This variation uses .Text instead of .Value, but either should work.

 

Dim MyFile As String
'
    MyFile = ThisWorkbook.Worksheets("Data").Range("J3").Text
'take a look at what is actually in MyFile at this point

MsgBox "File path and name is " & vbCrLf & "[" & MyFile & "]"

 

That MsgBox line will show you exactly what is in MyFile at that point before continuing.  If it is not what you expect, then we've more work to do.  If the file to be opened is not in the current directory, then you would need the full path to the file along with its name.

 

Now you said that the error was at this line of code:

Windows(MyFile).Activate

And that makes me realize that I misinterpreted your problem initially, and I think that TasosK made the same mistake... read on...

If the MsgBox line above does show that the entire path to the file is now in MyFile, that has to be reduced to JUST the filename before your problem line of code:

 

Dim MyFileName As String

MyFileName = Right(MyFile, Len(MyFile) - InStrRev(MyFile, Application.PathSeparator))

Windows(MyFileName).Activate

 

or you can write it all as one line if that works:

Windows(Right(MyFile, Len(MyFile) - InStrRev(MyFile, Application.PathSeparator))).Activate


 

I am free because I know that I alone am morally responsible for everything I do. R.A. Heinlein

Did this solve your problem?

Sorry this didn't help.

Great! Thanks for marking this as the answer.

How satisfied are you with this reply?

Thanks for your feedback, it helps us improve the site.

How satisfied are you with this response?

Thanks for your feedback.