none
VBA Run Time Error 9 when deleting a worksheet in Excel 2010 RRS feed

  • Question

  • Hello,

    I'm updating an exisiting macro which formats an existing report, deletes all rows under a certain dollar amount then saves the file to SharePoint. The macro was working fine, but now the manager wants two files, one over 2500 and a separate file for items under 2500.

    I tried walking through the updated macro but am getting a "Run-time error '9' Subscript out of range" error on a line that deletes two worksheets on the original file. What am I doing wrong???

    Dim FileName As String
    Dim fso
    Dim SourceBook, NewBook As Workbook 'NEW Code

    Sub HighandMedDBdollar()


    Dim LastRow As String 'NEW code

    Set SourceBook = ActiveWorkbook 'NEW code

    'orginal
    Set fso = CreateObject("Scripting.FileSystemObject")


    Application.ScreenUpdating = False
    Application.EnableEvents = False

    Application.DisplayAlerts = False
    Sheets("Cumulative Weekly Summary").Delete '<<<<<<< Error appears on this line of code
    Sheets("Status Summary").Delete
    Sheets("Detail").Columns("A:C").EntireColumn.Delete
    Sheets("Detail").Columns("F:F").EntireColumn.Delete
    Sheets("Detail").Rows("1:1").EntireRow.Delete
    Columns("L").ColumnWidth = 60.85
    Range("L1").Select
    ActiveCell.FormulaR1C1 = "Comments"
    ActiveCell.Interior.ColorIndex = 15

    With Range("A1:L1")
            .HorizontalAlignment = xlCenter
            .Font.Bold = True
    End With
        ActiveWorkbook.Worksheets("Detail").Sort.SortFields.Clear
        ActiveWorkbook.Worksheets("Detail").Sort.SortFields.Add Key:=Range("H:H") _
            , SortOn:=xlSortOnValues, Order:=xlDescending, DataOption:=xlSortNormal
    With ActiveWorkbook.Worksheets("Detail").Sort
        .SetRange Range("A1:L1950")
        .Header = xlYes
        .MatchCase = False
        .Orientation = xlTopToBottom
        .SortMethod = xlPinYin
        .Apply
    End With

    'NEW code to create 2nd workbook for medium dollars
    SourceBook.Sheets("Detail").Range("2:" & SourceBook.Sheets("Detail").Cells.CurrentRegion.Rows.Count).Copy
    Set NewBook = Workbooks.Add
    ActiveSheet.Paste
    ActiveSheet.Name = "Detail"

    'original code to sort by dollar amount
    With Range(Range("H1"), Range("H50000").End(xlUp))
        .AutoFilter field:=8, Criteria1:=">2500"
        .Offset(1).SpecialCells(xlCellTypeVisible).EntireRow.Delete
        .AutoFilter
    End With

    'NEW code to save 2nd file to SharePoint
    NewBook.Sheets(1).Select
    ActiveWorkbook.SaveAs FileName:= _
        "https://one.companyname.net/sites/vbanewbie/needsyourhelp/DB Medium-Low Dollars Status/Medium-Low Dollar DB_Unallocated as of " & Format(PrevBusDay, "mm-dd-yy") & ".xls" _
        , FileFormat:=xlExcel8, Password:="", WriteResPassword:="", _
        ReadOnlyRecommended:=False, CreateBackup:=False
    NewBook.Close

    'original code
    Application.ScreenUpdating = True
    Application.EnableEvents = True


    'code to go back to High Dollar file
    SourceBook.ActiveSheet.ShowAllData


    'Filter and delete code
    With Range(Range("H1"), Range("H50000").End(xlUp))
        .AutoFilter field:=8, Criteria1:="<2500"
        .Offset(1).SpecialCells(xlCellTypeVisible).EntireRow.Delete
        .AutoFilter
    End With

    Application.ScreenUpdating = True
    Application.EnableEvents = True

    ActiveSheet.Range("A1:L1").AutoFilter
    ActiveCell.Interior.ColorIndex = 19

    'Vlookup code to pull over comments from prior business days file step 2

    With ActiveWorkbook.Worksheets("Detail").Select
        Range("L2").Select
        ActiveCell.FormulaR1C1 = _
            "=VLOOKUP(RC[-255],'[High Dollar DB_Unallocated as of " & x & ".xls]Detail'!R1C1:R65536C12,12,FALSE)"
        Range("L2" & ActiveSheet.UsedRange.Rows.Count - 1).Select
        Selection.Copy
        LastRow = ActiveSheet.Cells(Rows.Count, "K").End(xlUp).Row + 1
        Range("K" & LastRow).Select
        ActiveSheet.Paste
       
    End With
       
    'original code to save 1st workbook to SharePoint
    ActiveWorkbook.SaveAs FileName:= _
            "https://one.companyname.net/sites/vbanewbie/needsyourhelp/DB High Dollar Status/High Dollar DB_Unallocated as of " & Format(PrevBusDay, "mm-dd-yy", "a") & ".xls" _
            , FileFormat:=xlExcel8, Password:="", WriteResPassword:="", _
            ReadOnlyRecommended:=False, CreateBackup:=False


    End Sub

    Function OpenLastFile()
        Application.DisplayAlerts = False
        Dim Path As String
        Path = "https://one.companyname.net/sites/vbanewbie/needsyourhelp/DB High Dollar Status/"
        Dim PrevDate As Date
        Dim file As String
        Dim ECount As Integer
        PrevDate = Date - 1
        ECount = 0
    TryAgain:
        On Error GoTo ErrorHandler
        PrevDate = PrevDate - 1
        file = Path & "High Dollar DB_Unallocated as of " & Format(PrevDate, "mm-dd-yy") & ".xls"
        Workbooks.Open (file)
        On Error GoTo 0
        Application.DisplayAlerts = True
        Exit Function
       
    ErrorHandler:
        ECount = ECount + 1
        If ECount < 10 Then
            GoTo TryAgain
        End If
        On Error GoTo 0
        Application.DisplayAlerts = True
        MsgBox ("Could not find valid last file")
    End Function


    Lorac1969


    • Edited by Lorac1969 Thursday, May 14, 2015 6:00 PM
    Thursday, May 14, 2015 5:56 PM

Answers

  • ​Hi Lorac1969,

    >>I tried walking through the updated macro but am getting a "Run-time error '9' Subscript out of range" error on a line that deletes two worksheets on the original file.

    This error message may be caused by that "You referenced a nonexistent collection member".

    I made a simple test with the code below:

    Sub deletesheet()
         Application.ScreenUpdating = False
         Application.EnableEvents = False
         Application.DisplayAlerts = False
         Sheets("Cumulative Weekly Summary").Delete
     End Sub

    I found that, if my sheet named "Cumulative Weekly Summary" exists in the excel file, it could be deleted. But if it did not exist in the excel file, I would get the same error with you.

    Based on your description, you want to delete two worksheets on the original file, and your code delete the file in the ActiveWorkbook. First of all, I will recommend you check whether the original file is active, and then check whether the Cumulative Weekly Summary worksheet exist in the activeworkbook.

    Best Regards,

    Edward

     

    We are trying to better understand customer views on social support experience, so your participation in this interview project would be greatly appreciated if you have time. Thanks for helping make community forums a great place.
    Click HERE to participate the survey.


    Friday, May 15, 2015 10:34 AM

All replies

  • ​Hi Lorac1969,

    >>I tried walking through the updated macro but am getting a "Run-time error '9' Subscript out of range" error on a line that deletes two worksheets on the original file.

    This error message may be caused by that "You referenced a nonexistent collection member".

    I made a simple test with the code below:

    Sub deletesheet()
         Application.ScreenUpdating = False
         Application.EnableEvents = False
         Application.DisplayAlerts = False
         Sheets("Cumulative Weekly Summary").Delete
     End Sub

    I found that, if my sheet named "Cumulative Weekly Summary" exists in the excel file, it could be deleted. But if it did not exist in the excel file, I would get the same error with you.

    Based on your description, you want to delete two worksheets on the original file, and your code delete the file in the ActiveWorkbook. First of all, I will recommend you check whether the original file is active, and then check whether the Cumulative Weekly Summary worksheet exist in the activeworkbook.

    Best Regards,

    Edward

     

    We are trying to better understand customer views on social support experience, so your participation in this interview project would be greatly appreciated if you have time. Thanks for helping make community forums a great place.
    Click HERE to participate the survey.


    Friday, May 15, 2015 10:34 AM
  • Hi Edward,

    You're right it doesn't recognize the worksheet, but ve the original file up (with the applicable tab) when I run the macro.

    Regards,

    Carol


    Lorac1969

    Friday, May 22, 2015 5:30 PM
  • Hi Carol,

    >> but ve the original file up (with the applicable tab) when I run the macro.

    What do you mean by this? Did your original issue still exist? If your original issue still exist, did you check the "Cumulative Weekly Summary" exist in your excel file? Could you share us more information?

    Best Regards,

    Edward


    We are trying to better understand customer views on social support experience, so your participation in this interview project would be greatly appreciated if you have time. Thanks for helping make community forums a great place.
    Click HERE to participate the survey.


    Monday, May 25, 2015 11:50 AM