Thread: Auto Sort

Hi everyone, back again, you guys helped me a lot yesterday.

My problem this time is in regards to auto sorting a table as data is changed in another worksheet and is auto copied within the said table.

I.E. I need the Fax page (second last), the table has to auto sort based on column " C ". This is easily done using custom sort but can it be done automatically ? I want to lock the page so nobody modifies it.

I attached a copy of my work so far, using Excel 2007.

All the sheets are locked except " Player DATA " and " Fax ".

The password is " Moose ".



Here is what I found . . .

here is another post in this forum that is exactly what I need but cannot get it to work.

http://www.excelforum.com/excel-2007...o-sorting.html

and here is a copy of the code I recorded using macro recorder ( Please be patient as I'm driving blind here )

Code:

Sub AutoSort()
'
' AutoSort Macro
'

'
    ActiveWindow.SmallScroll Down:=-12
    Range("A5:C29").Select
    ActiveWorkbook.Worksheets("Fax").Sort.SortFields.Clear
    ActiveWorkbook.Worksheets("Fax").Sort.SortFields.Add Key:=Range("C5:C29"), _
        SortOn:=xlSortOnValues, Order:=xlDescending, DataOption:=xlSortNormal
    With ActiveWorkbook.Worksheets("Fax").Sort
        .SetRange Range("A5:C29")
        .Header = xlGuess
        .MatchCase = False
        .Orientation = xlTopToBottom
        .SortMethod = xlPinYin
        .Apply
    End With
End Sub

so to recapitulate, I need the Fax page to update (auto sort) when the data is changed in the " Player DATA " worksheet.

Yes, macros would be simplest. I'll offer that.

1) Right-click on the FAX tab and select VIEW CODE
2) Paste in this sheet activation macro:

Code:

Option Explicit

Private Sub Worksheet_Activate()
    Sheets("Player DATA").Range("B3:B27").Copy
    Range("B5").PasteSpecial xlPasteValues
    Sheets("Player DATA").Range("AL3:AL27").Copy
    Range("C5").PasteSpecial xlPasteValues
    Range("A5:C29").Sort Key1:=Range("A5"), Order1:=xlAscending, Header:=xlNo, _
        OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _
        DataOption1:=xlSortNormal
End Sub

3) Close the editor
4) Save your sheet as a macro-enabled workbook (*.xlsm)
5) Now each time you bring up the FAX sheet onscreen it will reassert the list in ascending order.

When you want to let macros free to do what they want on protected sheets, give them global permission one-time when you first open the workbook, then you don't have to make every macro deal with it.

Put this in the ThisWorkbook module, it will make the Player DATA sheet protected from users, but not from macros.

Code:

Private Sub Workbook_Open()
    Sheets("Player Data").Protect "Password", UserInterfaceOnly:=True
End Sub

Of course, set your own password...

If that takes care of your need, be sure to EDIT your original post, click Go Advanced and mark the PREFIX box [SOLVED].


(Also, use the blue "scales" icon in our posts to leave Reputation Feedback, it is appreciated. It is found across from the "time" in each of our posts.)