Run time error 9 received in running a macro

[dellennon]

Hi all, Please help me to troubleshoot to run a macro that is having an error message of "run time error 9, subscription out of range".

Debug is error on this line..

For x = 1 To 3

Application.DisplayAlerts = False
Blank_DBoR_Workbook.Sheets("Sheet" & x).Delete
Application.DisplayAlerts = True

Next



Thanks.

 

[Trebor76]

Hi dellennon,

Welcome to the forum!!

There must be no tab called "Sheet" & x to delete - perhaps because the macro has run once and already deleted the three tabs?

HTH

Robert

 

[dellennon]

Hi Robert,
Thanks for your reply. Sorry for my ignorance since this macro was only given to me by my friend, so how can i fix the problem? erase the command line that causing the problem?

 

[dellennon]

by the way, this macro works on other pc but not on some...

 

[Trebor76]

Hi there,

I dare it is working erratically because sometimes there's tabs called 'Sheet1', 'Sheet2' and 'Sheet3' to be deleted, and sometimes there's not - no doubt after the macro has run.

You can use the following that simply suppresses the error message but in no way fixes the issue:

    For x = 1 To 3
        On Error Resume Next
            Application.DisplayAlerts = False
                Blank_DBoR_Workbook.Sheets("Sheet" & x).Delete
            Application.DisplayAlerts = True
        On Error GoTo 0
    Next

HTH

Robert

 

[dellennon]

Hi there,

I dare it is working erratically because sometimes there's tabs called 'Sheet1', 'Sheet2' and 'Sheet3' to be deleted, and sometimes there's not - no doubt after the macro has run.

You can use the following that simply suppresses the error message but in no way fixes the issue:

    For x = 1 To 3
        On Error Resume Next
            Application.DisplayAlerts = False
                Blank_DBoR_Workbook.Sheets("Sheet" & x).Delete
            Application.DisplayAlerts = True
        On Error GoTo 0
    Next

HTH

Robert

Thanks again Robert. I did try your solution. The Macro runs smoothly but it did not produce the output file that it supposed to create... What might be the problem if this macro works on others pc/excel but not on mine and some others... We have the same excel version..

 

[Trebor76]

What might be the problem

Good question as it worked fine for me

What exactly do you want the macro to do, and what is being assigned to the 'Blank_DBoR_Workbook' variable?

 

[dellennon]

Good question as it worked fine for me

What exactly do you want the macro to do, and what is being assigned to the 'Blank_DBoR_Workbook' variable?

The macro will compare two excel tables and the output will be the same format as the two excel but highlighted the mismatched value/s. Blank Dbor Workbook should be the resulting WB. Below is the complete module that the error is occuring..



Private Sub Create_Blank_DBoR()

Dim x As Integer

Set Blank_DBoR_Workbook = Workbooks.Add

'copy the DBoR sheets to a new workbook
For x = UBound(arraySheetSelector) To LBound(arraySheetSelector) Step -1
Comparison_Robot_Workbook.Worksheets(arraySheetSelector(x)).Copy Before:=Blank_DBoR_Workbook.Sheets(1)
Next

'delete empty sheets
For x = 1 To 3

Application.DisplayAlerts = False
Blank_DBoR_Workbook.Sheets("Sheet" & x).Delete
Application.DisplayAlerts = True


Next

 

[Trebor76]

Hi dellennon,

It may be that the new workbook being created doesn't actually three blank tabs to start with as this is an Excel setting that can be easily changed per user (PC).

As such, try this which will delete any tab in the new workbook that has no data - not just 'Sheet1', 'Sheet2', or 'Sheet3':

Private Sub Create_Blank_DBoR()

    Dim x As Integer
    
    Set Blank_DBoR_Workbook = Workbooks.Add
    
    'copy the DBoR sheets to a new workbook
    For x = UBound(arraySheetSelector) To LBound(arraySheetSelector) Step -1
        Comparison_Robot_Workbook.Worksheets(arraySheetSelector(x)).Copy Before:=Blank_DBoR_Workbook.Sheets(1)
    Next
    
    'Delete empty sheets
    For Each Worksheet In Blank_DBoR_Workbook.Sheets
        If WorksheetFunction.CountA(Sheets(Worksheet.Name).Cells) = 0 Then
            Application.DisplayAlerts = False
                Blank_DBoR_Workbook.Sheets(Worksheet.Name).Delete
            Application.DisplayAlerts = True
        End If
    Next Worksheet

End Sub

HTH

Robert