Auto Sort

**bocian** · 05-18-2012, 03:02 PM

Hi,

Currently any time I add a number in worksheet one it automatically updates on worksheet 2. I would like to be able automatically sort worksheet 2 based on the new numbers as they are added. Is this possible. While I'm pretty proficient in Excel, I have been trying to figure out how to make this work for hours to no avail. I know I can data sort each time, but it's a lot of information and I'd like it to just do it for me

Any help is greatly appreciated!!

Lorin

**kpark91** · 05-18-2012, 03:09 PM

How was this auto-update done?
May I view the code so that I can make sure it doesn't interfere with it?

In what order is the data sorted? Provide an example.

and finally, if you can attach your workbook or dummy workbook so that we have easier time understanding which column you are trying to sort in what ways and such.

**bocian** · 05-18-2012, 03:24 PM

Hi,
I'm attaching a dummy workbook. Feel free to mess around with it...it's updated by the sum function. I got this worksheet from somebody and they asked me to work it so it's mostly automated...any suggestions are greatly appreciated!!

Lorin

Originally Posted by kpark91

How was this auto-update done?
May I view the code so that I can make sure it doesn't interfere with it?

In what order is the data sorted? Provide an example.

and finally, if you can attach your workbook or dummy workbook so that we have easier time understanding which column you are trying to sort in what ways and such.

**Pichingualas** · 05-18-2012, 04:12 PM

This should do what you want:

Private Sub Worksheet_Change(ByVal Target As Range)
    Dim LastRow As Long
    Dim ws As Worksheet
    
    Set ws = Sheets(2)
    
    LastRow = ws.UsedRange.Rows.Count
    
    With ws.Sort
        .SortFields.Clear
        .SortFields.Add Key:=Range("C8:C" & LastRow) _
        , SortOn:=xlSortOnValues, Order:=xlDescending, DataOption:=xlSortNormal
        .SetRange Range("A8:C" & LastRow)
        .Header = xlGuess
        .MatchCase = False
        .Orientation = xlTopToBottom
        .SortMethod = xlPinYin
        .Apply
    End With
End Sub

You have to place the code in the module that corresponds to sheet1, check the attachment to see how.
dummyworkbook.xlsm

**bocian** · 05-21-2012, 07:51 PM

Wow, thank you so much!! that's exactly what I needed. The only thing is it's a little glitchy because if I enter something on the first page the second page superimposes on it. that's fine, I'm just trying to learn this, so I was wondering why that would happen.
thank you thank you thank you!!

**Pichingualas** · 05-21-2012, 09:01 PM

That's because you have to stop the screen from udating so it won't show what's happening. Use this:

Private Sub Worksheet_Change(ByVal Target As Range)
    Dim LastRow As Long
    Dim ws As Worksheet
    
    Application.ScreenUpdating = False    
    
    Set ws = Sheets(2)
    
    LastRow = ws.UsedRange.Rows.Count
    
    With ws.Sort
        .SortFields.Clear
        .SortFields.Add Key:=Range("C8:C" & LastRow) _
        , SortOn:=xlSortOnValues, Order:=xlDescending, DataOption:=xlSortNormal
        .SetRange Range("A8:C" & LastRow)
        .Header = xlGuess
        .MatchCase = False
        .Orientation = xlTopToBottom
        .SortMethod = xlPinYin
        .Apply
    End With
    
    Application.ScreenUpdating = True
    
End Sub

Lines added in blue.