Consulting

Results 1 to 6 of 6

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

  1. #1

    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?

  2. #2
    VBAX Tutor paulked's Avatar
    Joined
    Apr 2006
    Posts
    280
    Location
    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.
    Semper in excretia sumus; solum profundum variat.

  3. #3
    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!

  4. #4
    VBAX Tutor paulked's Avatar
    Joined
    Apr 2006
    Posts
    280
    Location
    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
    Semper in excretia sumus; solum profundum variat.

  5. #5
    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.
    Attached Images Attached Images
    Last edited by elusivefds; 01-14-2018 at 09:09 PM.

  6. #6
    VBAX Tutor paulked's Avatar
    Joined
    Apr 2006
    Posts
    280
    Location
    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.
    Semper in excretia sumus; solum profundum variat.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •