View Full Version : When changing Row B in Sheet 2 automatically do sorting with Row B in Sheet 1
elusivefds
01-12-2018, 12:25 AM
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
01-12-2018, 01:28 AM
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
01-12-2018, 02:25 AM
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
01-12-2018, 02:52 AM
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
01-14-2018, 06:37 PM
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
01-14-2018, 10:27 PM
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.
Powered by vBulletin® Version 4.2.5 Copyright © 2024 vBulletin Solutions Inc. All rights reserved.