Runtime Errror 9 Subscript out of range

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






Attachment 12670

Code:

---

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

---

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.

I think you meant

Code:

---

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

---

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

Code:

---

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

Code:

---

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

---

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

Code:

---

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

---

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

Thanks again,

Phan Nguyen