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
    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 
    
    
    Formatting tags added by mark007
    Sheet2 code

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

    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 
    
    
    Formatting tags added by mark007
    To sort when you open the workbook:

    ThisWorkbook code

    Private Sub Workbook_Open() 
        SortS1B 
    End Sub 
    
    
    Formatting tags added by mark007

    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
    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 
    
    
    Formatting tags added by mark007
    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
      To view attachments your post count must be 0 or greater. Your post count is 0 momentarily.
    Last edited by elusivefds; 01-14-2018 at 09:09 PM.

  6. #6
    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
  •