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

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?

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

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

Code:

---

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

---

Module1 code

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

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.

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!

Code:

---

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

---

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.

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.