Run Time Error 9. Debugging

[davso87]

Hi again all,

I currently have 4 seperate reports going into 1 main report to give me information i need, i have started doing a macro but this once error shows and im not sure what i can do to solve it.

its quite a big sheet.... the debug is highlighted in red.

Sub Macro4()
'
' Macro4 Macro
'
' Keyboard Shortcut: Ctrl+m
'
        Workbooks.Open Filename:="X:\ADMI-2015 Empty Location Report.xls"
    Range("A:A,C:C").Select
    Range("C2").Activate
    Selection.Delete Shift:=xlToLeft
    Cells.Select
    Range("A2").Activate
    With Selection
        .WrapText = False
        .Orientation = 0
        .AddIndent = False
        .ShrinkToFit = False
        .MergeCells = False
    End With
    Cells.EntireColumn.AutoFit
    Cells.EntireRow.AutoFit
    Rows("1:1").Select
    Selection.Delete Shift:=xlUp
    Cells.Select
    ActiveWorkbook.Worksheets("Sheet1").Sort.SortFields.Clear
    ActiveWorkbook.Worksheets("Sheet1").Sort.SortFields.Add Key:=Range("B2:B6041" _
        ), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:= _
        xlSortTextAsNumbers
    ActiveWorkbook.Worksheets("Sheet1").Sort.SortFields.Add Key:=Range("A2:A6041" _
        ), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
    With ActiveWorkbook.Worksheets("Sheet1").Sort
        .SetRange Range("A1:I6041")
        .Header = xlYes
        .MatchCase = False
        .Orientation = xlTopToBottom
        .SortMethod = xlPinYin
        .Apply
    End With
    Range("B1").Select
    Selection.End(xlDown).Select
    Range("A3177").Select
    Range(Selection, Selection.End(xlDown)).Select
    Workbooks.Open Filename:="X:\ADMI-2034 Bin Setup.xls"
    Columns("A:A").Select
    Selection.Delete Shift:=xlToLeft
    Cells.Select
    With Selection
        .WrapText = False
        .Orientation = 0
        .AddIndent = False
        .ShrinkToFit = False
        .MergeCells = False
    End With
    Cells.EntireColumn.AutoFit
    Cells.EntireRow.AutoFit
    Rows("1:1").Select
    Selection.Delete Shift:=xlUp
    Range("A1").Select
    Selection.End(xlDown).Select
    Range("A7152").Select
    Windows("ADMI-2015 Empty Location Report.xls").Activate
    Selection.Copy
[COLOR=#ff0000]    Windows("ADMI-2034 Bin Setup.xls").Activate[/COLOR]
    ActiveSheet.Paste
    Cells.Select
    Range("A7114").Activate
    Application.CutCopyMode = False
    ActiveWorkbook.Worksheets("Sheet1").Sort.SortFields.Clear
    ActiveWorkbook.Worksheets("Sheet1").Sort.SortFields.Add Key:=Range( _
        "A2:A10014"), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:= _
        xlSortNormal
    With ActiveWorkbook.Worksheets("Sheet1").Sort
        .SetRange Range("A1:T10014")
        .Header = xlYes
        .MatchCase = False
        .Orientation = xlTopToBottom
        .SortMethod = xlPinYin
        .Apply
    End With
    Range("A2").Select
    ActiveWindow.Close
    Workbooks.Open Filename:="X:\Location list.xls"
    Selection.Copy
    Windows("ADMI-2034 Bin Setup.xls").Activate
    ActiveSheet.Paste
    Columns("B:F").Select
    Application.CutCopyMode = False
    Selection.Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrAbove
    Columns("A:A").Select
    Selection.Copy
    Range("B1").Select
    ActiveSheet.Paste
    Range("B1").Select
    Application.CutCopyMode = False
    Selection.ClearContents
    Columns("B:B").Select
    Selection.TextToColumns Destination:=Range("B1"), DataType:=xlFixedWidth, _
        FieldInfo:=Array(Array(0, 1), Array(1, 1), Array(2, 1), Array(3, 1), Array(6, 1)), _
        TrailingMinusNumbers:=True
    Cells.Select
    Cells.EntireColumn.AutoFit
    Columns("N:N").Select
    Selection.Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrAbove
    Range("N1").Select
    ActiveCell.FormulaR1C1 = "sku"
    Range("N2").Select
    ActiveCell.FormulaR1C1 = "=RC[-2]&RC[-1]"
    Range("N2").Select
    Selection.AutoFill Destination:=Range("N2:N10018")
    Range("N2:N10018").Select
    Calculate
    Range("N2:N10018").Select
    Range("N3").Activate
    ActiveWindow.LargeScroll ToRight:=1
    Columns("Y:AA").Select
    Selection.Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrAbove
    Columns("X:X").Select
    Selection.Copy
    Columns("Y:Y").Select
    ActiveSheet.Paste
    Range("Y1").Select
    Application.CutCopyMode = False
    Selection.ClearContents
    Columns("Y:Y").Select
    Selection.TextToColumns Destination:=Range("Y1"), DataType:=xlFixedWidth, _
        FieldInfo:=Array(Array(0, 1), Array(1, 1), Array(2, 1)), TrailingMinusNumbers:= _
        True
    Columns("AB:AB").Select
    Range(Selection, Cells(1)).Select
    Selection.Copy
    Workbooks.Open Filename:="X:\Intermediate ADMI.xls"
    Sheets("Active Locations").Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    Application.CutCopyMode = False
    ActiveWorkbook.Save
    Sheets("Finished").Select
    Selection.Copy
    Windows("Copy of Finished.xls").Activate
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    Application.CutCopyMode = False
    ActiveWorkbook.Save
End Sub

 

[StephenCrump]

Are you sure it's that line giving the error?

You have an identical line further down in the code, which occurs after you have closed the window

Windows("ADMI-2034 Bin Setup.xls").Activate
'...
ActiveWindow.Close
Workbooks.Open Filename:="X:\Location list.xls"
Windows("ADMI-2034 Bin Setup.xls").Activate 'but you've just closed this window!

Just a guess, but perhaps you want to be working with X:\Location list.xls in this next block of code?