Autosort on different worksheet cell change

AirBrun · Oct 25th 2010

Hello,

New to the forums but I have read many of them dealing with my topic. Sadly i have been struggling with this for hours and I can't seem to get it to work. Any help would be greatly appreciated! Attached are a sample of my workbooks and sheets.

What I want to happen is for column F in the Sponsors worksheet in forumhelp.xlsm to sort A-Z when the cell B3 changes in the New Hires worksheet. Based on the B3 value it will change the values in column F. I have them change to "zzzz" in column F to rid the problem of "Cells are not empty if they contain functions" issue. Not sure if I'm going about this in the wrong way or not - but any help would be great. Thanks!

Rob Xaos · Oct 26th 2010

Re: Autosort on different worksheet cell change

Uploaded forumhelp.xlsm Workbook has #REF errors in the column F formulas which makes it a bit tricky to test. However I think what you need is:

In Worksheet "New Hires" code module:

Code

Option Explicit
 
Private Sub Worksheet_Change(ByVal Target As Range)
 
    If Target.Address = "$B$3" Then
        ActiveWorkbook.Worksheets("Sponsors").Sort.SortFields.Clear
        ActiveWorkbook.Worksheets("Sponsors").Sort.SortFields.Add Key:=Range("F2:F6") _
            , SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
        With ActiveWorkbook.Worksheets("Sponsors").Sort
            .SetRange Range("A1:F6")
            .Header = xlYes
            .MatchCase = False
            .Orientation = xlTopToBottom
            .Apply
        End With
    End If
End Sub

Display More

Autosort on different worksheet cell change

Participate now!

Share