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.