When changing Row B in Sheet 2 automatically do sorting with Row B in Sheet 1

[elusivefds]

I would like to do something like when changing Row B in Sheet 2 automatically do sorting with Row B in Sheet 1

I am currently using these codes

Private Sub Worksheet_Change(ByVal Target As Range)
On Error Resume Next
If Not Intersect(Target, Range("B:B")) Is Nothing Then
Range("B1").Sort Key1:=Range("B2"), _
Order1:=xlAscending, Header:=xlYes, _
OrderCustom:=1, MatchCase:=False, _
Orientation:=xlTopToBottom
End If
End Sub

However, I find that the items will not automatically sort when I open the file/ when there are changes made in Sheet 1.
I have to enter a new number in Row B in Sheet 2 everytime so that it starts the VBA codes and do an automatic sorting.

Any solution?

[paulked]

Hi there and welcome to the forum.

The WorkSheet_Change event is only triggered by a change in the sheet to which it attached. If you want to sort Sheet1 with a change in either Sheet1 or Sheet 2 then you would have to have a WorkSheet_Change event in both of the sheet modules that call a common 'sort' sub in a code module. Something like (Not tested):

Sheet1 code

Public Sub WorkSheet_Change(ByVal Target As Range)
    If Not Intersect(Target, Range("B:B")) Is Nothing Then SortS1B 'Or whatever criteria you want to detect in sheet1
End Sub

Sheet2 code

Public Sub WorkSheet_Change(ByVal Target As Range)
    If Not Intersect(Target, Range("B:B")) Is Nothing Then SortS1B 
End Sub

Module1 code

Sub SortS1B()
    Application.EnableEvents = False
    Sheet1.Range("B1").Sort Key1:=Range("B2"), Order1:=xlAscending, Header:=xlYes, _
                  OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom
    Application.EnableEvents = True
End Sub

To sort when you open the workbook:

ThisWorkbook code

Private Sub Workbook_Open()
    SortS1B
End Sub

Best regards

Paul Ked

PS, please use the Code tags to wrap code, it makes for much easier reading.

[elusivefds]

what if i want to sort sheet 1 from file A base on the data from sheet 2 from file B (i.e. another excel file)?
how should i adjust the code? thanks!

[paulked]

Sub SortS1B()
    Application.EnableEvents = False
    Workbooks("BookA.xlsm").Sheets("Sheet1").Range("B1").Sort Key1:=Range("B2"), Order1:=xlAscending, Header:=xlYes, _
    OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom
    Application.EnableEvents = True
End Sub

[elusivefds]

Thanks for your help and it works!
but need to specify the range for the sheet. otherwise, the problem shown in the attachments will appear.

[paulked]

The range to sort is the same as you provided in your op. Record a macro of the sort you want and replace the code with that.