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






12670



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