auto-sort for excel 2010

[ISSOWAVES]

Hello,

I'm making a task-list with excel 2010. I want the data I input into column B to auto-sort all column to E. So it will start with B finish with E.

As for the # of rows, it will start from row 2 and finish at row 35.

Is there a way to auto-sort this information whenever new data is inserted. We choose new data by the drop down menu in column B.

Any help will be greatly appreciated it.

 

[VoG]

Try this: right click the sheet tab, select View Code and paste in

Private Sub Worksheet_Change(ByVal Target As Range)
If Intersect(Target, Range("B2:B35")) Is Nothing Then Exit Sub
Application.EnableEvents = False
Me.Sort.SortFields.Clear
Me.Sort.SortFields.Add Key:=Range("B2:B35"), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
With Me.Sort
    .SetRange Range("B2:E35")
    .Header = xlGuess
    .MatchCase = False
    .Orientation = xlTopToBottom
    .SortMethod = xlPinYin
    .Apply
End With
Application.EnableEvents = True
End Sub

 

[ISSOWAVES]

Hi Peter,

Yes! it worked. Thanks very much for your help.

One other thing, I have two task list on one sheet. The code you provided works for the first task list, but I hope it can also work for the second task list.

The other task list is at located at:
column G:J
row 2:35

 

[ISSOWAVES]

i had trouble posting this, which is why i accidentally posted it twice. i couldn't figure out how to delete it.

 

[ISSOWAVES]

hope you or somebody can still help with the second task list located at:

column G:J
row 2:35

 

[VoG]

Try

Private Sub Worksheet_Change(ByVal Target As Range)
If Not Intersect(Target, Range("B2:B35")) Is Nothing Then
    Application.EnableEvents = False
    Me.Sort.SortFields.Clear
    Me.Sort.SortFields.Add Key:=Range("B2:B35"), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
    With Me.Sort
        .SetRange Range("B2:E35")
        .Header = xlGuess
        .MatchCase = False
        .Orientation = xlTopToBottom
        .SortMethod = xlPinYin
        .Apply
    End With
    Application.EnableEvents = True
ElseIf Not Intersect(Target, Range("G2:G35")) Is Nothing Then
    Application.EnableEvents = False
    Me.Sort.SortFields.Clear
    Me.Sort.SortFields.Add Key:=Range("G2:G35"), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
    With Me.Sort
        .SetRange Range("G2:J35")
        .Header = xlGuess
        .MatchCase = False
        .Orientation = xlTopToBottom
        .SortMethod = xlPinYin
        .Apply
    End With
    Application.EnableEvents = True
End If
End Sub

 

[ISSOWAVES]

Peter,

Wow! great and awesome. That's it.

Super thanks. I hope I'll be an expert at Excel in the near future.

 

[zh2os]

I am having a similar problem but we would perfer to have it automatically sort our custom sort which is currently column M Pending, Working, Yes, No then by Column D Oldest to newest, then by Column F Smallest to Largest, then by Column D Newest to Oldest. Any help you can provide would be greatly appreciated. If needed I can send you the spreadsheet we work with and you are free to work with it from there.

 

[Toonses]

Hello, This was a great tip. Further to this thread I am creating an excel form (one worksheet) for a customer that is requesting auto sort, however the form has seven different sections to apply the auto sort to (or code) - but I have no idea how big of a range he requires for each section. Is there a way he can add new entries and the range expands or does this have to be a pre-set range to start with?

Thank you.

 

[moooo]

Hi. I had the same problem and this macro solved my problem, but after some changes it didn't work any more.I tried to find my problem but i couldn't. This macro is working in specific situation that I didn't care?