run-time error \'9\' subscript out of range I am getting the run time error 9 fo

Question

run-time error '9' subscript out of range

I am getting the run time error 9 for the following code:

'create data collection workbook for next month
    OriginalWorkbook = ActiveWorkbook.Name
    Workbooks.Add
    NewWorkbook = ActiveWorkbook.Name
    Workbooks(OriginalWorkbook).Activate
'list of reports to create
    Sheets(MAdmin).Select
    ActiveSheet.PivotTables("PivotTable1").RefreshTable
    Application.GoTo Reference:="DC_First_Row"
    First_Row = ActiveCell.Row
    If Cells(ActiveCell.Row + 1, 6) = "" Then
        Last_Row = ActiveCell.Row
    Else
        Selection.End(xlDown).Select
        Last_Row = ActiveCell.Row
    End If
'loop through each report
    For I = First_Row To Last_Row
        SName = Sheets(MAdmin).Range("F" & I).Value
        'MsgBox SName
    'Copy the current the Metric Report to the new workbook
        Workbooks(OriginalWorkbook).Activate
        Sheets(SName).Visible = True
        Sheets(SName).Select
        Selection.AutoFilter Field:=1 'undo any filtering, so the copy can work correctly
        Sheets(SName).Copy After:=Workbooks(NewWorkbook).Sheets(1)
    'Set up data collection form by saving values and deleting the history
        Workbooks(NewWorkbook).Activate
        Sheets(SName).Select
        Range("D5").Select
        Selection.End(xlDown).Select
        LastDCRow = ActiveCell.Row
        Range("A1:F" & LastDCRow).Select
        Selection.Copy
        Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:= _
            False, Transpose:=False
        Range("E6:F" & LastDCRow).Select
        Application.CutCopyMode = False
        Selection.ClearContents
        Columns("N:N").Select
        Range(Selection, Selection.End(xlToRight)).Select
        Selection.Delete Shift:=xlToLeft
        Range("A1").Select
        'remove the macro buttons
        ActiveSheet.Shapes("Chart").Select
        Selection.Delete
        ActiveSheet.Shapes("Home").Select
        Selection.Delete
        ActiveSheet.Shapes("Level1and2").Select
        Selection.Delete
        ActiveSheet.Shapes("Detail").Select
        Selection.Delete
        'Delete the range names
        Do Until Names.Count = 0
            On Error GoTo N1
            'MsgBox (Names(Names.Count))
            Names(Names.Count).Delete
        Loop
N1:
        'retun the sheet to the initial rows and columns
        Range("A6").Select 'This is the first row of data
        ActiveWindow.ScrollColumn = 1
        ActiveWindow.ScrollRow = 1
        'return to the orginal workbook and report status
        Workbooks(OriginalWorkbook).Activate
        Sheets(SName).Visible = False
        Sheets("Administration").Select
        Range("DCStatus").Value = SName & " Completed"
    Next I
'remove the orginal blank worksheet from the file
    Workbooks(NewWorkbook).Activate
    Sheets("Sheet1").Select                                                   <-- this is the line erroring out
    ActiveWindow.SelectedSheets.Delete

I already checked the sheet name and there is no blank.

the new sheet created has its own sheet name. just want to delete all the automatically created blank sheets in excel, e.g. Sheet 1, Sheet 2...

Thank you for help !!

Explanation / Answer

Hi..

The error number indicates that the sheet name in the Active Workbook does not exist. I know that you have said that you checked for blanks etc in the name but did you check that you have the correct workbook activated? Try the following when the code stops:

While still in the VBA editor window without changing back to the worksheet.

Use Ctrl and G to open the immediate window.

Type in the following in the immediate window and then press Enter.

? activeworkbook.Name

Ensure that it returns the correct workbook name for the variable "NewWorkbook".;

If the correct workbook name is returned then type in the Immediate window type in the following and press Enter and see if that returns an error..

Sheets("Sheet1").Select

If all else fails then you can also use the following code to change to the workbook and the worksheet in one line of code so that might be worth a try also..

Application.Goto Workbooks(NewWorkbook).Sheets("Sheet1").Range("A1").

Related Questions

Navigate

• Browse (All)
• Browse R
• Subjects

Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.