Runtime Errror 9 Subscript out of range

[phan913]

I need some help I am trying to automatically sort 2 columns B and C when I update another sheet. When I update another sheet, sheet 1 updates through a formula but I keep getting Runtime Errror 9 Subscript out of range. Any help will be appreciated.

thanks,

Phan






Capture.jpg

Private Sub Worksheet_Calculate()
ThisWorkbook.Activate
Application.Workbooks("ThisWorkbook").Sheets("1").Range("B6:C100").Sort Key1:=Range("B1"), Order1:=xlAscending, Header:=xlYes
End Sub

[Kenneth Hobs]

You have an open workbook with a name of "ThisWorkbook"? ThisWorkbook is an object of the current workbook. Just use ThisWorkbook or even ActiveWorkbook if needed. In a like manner, you have a sheet named, "1"? While that is possible, the usual is "Sheet1". Whatever string value you have the sheet's tab, that is what you use for the string for Sheets() or WorkSheet().

I don't see a need to activate the current workbook which would be ThisWorkbook.

I can be more exact if you would work up a very simple workbook and attach it.

[Paul_Hossler]

I think you meant

Private Sub Worksheet_Calculate()
    ThisWorkbook.Activate
    ThisWorkbook.Sheets("1").Range("B6:C100").Sort Key1:=Range("B1"), Order1:=xlAscending, Header:=xlYes
End Sub

[Paul_Hossler]

Although a lot of that is probably redundant since Excel's assumed defaults are usually pretty accurate

Private Sub Worksheet_Calculate()
    Sheets("1").Range("B6:C100").Sort Key1:=Range("B1"), Order1:=xlAscending, Header:=xlYes
End Sub

If Sheets("1") module contains the code, then this should also work

Private Sub Worksheet_Calculate()
    Range("B6:C100").Sort Key1:=Range("B1"), Order1:=xlAscending, Header:=xlYes
End Sub

[Bob Phillips]

Personally, I never trust the defaults, I would rather be fully explicit myself, full qualify ranges, and cancel events

Private Sub Worksheet_Calculate()
    
    On Error GoTo wsc_exit
    
    Application.EnableEvents = False
    
    With ThisWorkbook.Worksheets("Sheet1")
    
        .Range("B6:C100").Sort Key1:=.Range("B1"), Order1:=xlAscending, Header:=xlYes
    End With
    
wsc_exit:
    Application.EnableEvents = True
End Sub

[phan913]

Thank you everyone for your suggestions. I was able to get it to work.

Thanks again,

Phan Nguyen