I am Hoping to Find a Sort Macro

[njpamdnc]

Hello...I am a schoolteacher who wants to provide students with a chance to retake failed tests, and I want the data organized by class periods.

I am requesting a macro to perform the following sort functions:


Sort 1 is ascending from A2 to AT205 sorting by Column AT(Test Score)


Sort 2 is ascending from A2 to whatever value in Column AT that is '69' or lower sorting by Column D(Class Period)

 

[Kenneth Hobson]

The easiest way to record a macro if you really need one.

1. Record the Sort ascending column AT.
2. Use AutoFilter or Advanced Filter and Filter in Place. The number filter or criterion would be for column 4, column D, "<=69".

I am thinking that in (1) you need to add a 2nd sort to sort by class period column. I would probably set that as the first sort. There might be a 3rd sort to sort by test name column.

With Advanced Filter, you could make it filter to a separate sheet for each period for a test name after the sort(s). The macro could delete any existing data if it exists. e.g. Sheet name: 1001, 2004, 3001, 4099, etc. The criterion might have the test name and score columns. That way, all you really need to do before running the macro would be to set the criteria for test name.

Manually, that takes about 15s for a simple sort, autofilter.

 

[njpamdnc]

I really do appreciate your quick and positive response. However, I was just hoping for someone to give me a simple macro to get the job done. After I copy and paste the test reports for all of my five classes onto an excel document, the only manual course of action I want to take is to execute the macro. Is such a request possible?

 

[Kenneth Hobson]

You could have posted the recorded macro. I did it below Main().

You can change activesheet to another worksheet not active like this:

Set ws = Worksheets("SomeOtherWorksheetTabNameHere")

You can also hard code the range. I set it to be dynamic.

As with any code, be sure to test on backup copy. A sort by macro can not be undone.

Sub Main()
    Dim r As Range, ws As Worksheet
    Set ws = ActiveSheet
    With ws
        Set r = .[A1].CurrentRegion
        .Sort.SortFields.Clear
        .Sort.SortFields.Add2 r.Columns(4), xlSortOnValues, xlAscending, xlSortNormal
        .Sort.SortFields.Add2 r.Columns(46), xlSortOnValues, xlAscending, xlSortNormal
        With .Sort
            .SetRange r
            .Header = xlYes
            .MatchCase = False
            .Orientation = xlTopToBottom
            .SortMethod = xlPinYin
            .Apply
        End With
        r.AutoFilter 46, "<=69"
    End With
End Sub

Sub Macro2()
'
' Macro2 Macro
'

'
    Range("A1:AT14").Select
    ActiveWorkbook.Worksheets("SortFilter").Sort.SortFields.Clear
    ActiveWorkbook.Worksheets("SortFilter").Sort.SortFields.Add2 Key:=Range( _
        "D2:D14"), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:= _
        xlSortNormal
    ActiveWorkbook.Worksheets("SortFilter").Sort.SortFields.Add2 Key:=Range( _
        "AT2:AT14"), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:= _
        xlSortNormal
    With ActiveWorkbook.Worksheets("SortFilter").Sort
        .SetRange Range("A1:AT14")
        .Header = xlYes
        .MatchCase = False
        .Orientation = xlTopToBottom
        .SortMethod = xlPinYin
        .Apply
    End With
    Selection.AutoFilter
    ActiveSheet.Range("$A$1:$AT$14").AutoFilter Field:=46, Criteria1:="<=69", _
        Operator:=xlAnd
End Sub