Greetings,
I need some help creating a macro to simplify a multi-level sorting process.

The spreadsheet looks like this:
Rows 1-4 are purely extraneous, not involved in the sort.
Row 5 contains header labels.
Rows 6-100 contain data. Each row consists of assignment scores for one student, one per column.

The users frequently need to sort the entire data set (i.e. all of rows 6-100) in different ways. What I’d like to do is create a generic macro for a multi-level sort as follows:
• The entire data set (all columns) would always be sorted
• The first level of the sort would be the currently-active column. In most cases this will be a particular assignment.
• The second level of the sort would be Column D, which contains a total sum of all the assignments.
• The third level of the sort would be Column C, which contains the student’s last name.

This would allow the users to quickly see, for any given assignment, who did the best on the assignment. For groups of students with the same score, they would be further sub-sorted by the student’s total. Any remaining ties would be sorted by last name. This is an action needed frequently, and I’d like to come up with a single macro that could be used by positioning the cursor on any of the assignment columns, then clicking a button to invoke the macro.

I’ve played around with recording macros. It’s obviously quite easy to record one for one particular column (example below, using column H), but I’m having trouble modifying it to work generically. I clearly need to change the “H6:H100” text to use the currently-active column itself, but don’t know how to do that. Suggestions appreciated!


Sub GenericSort()
'
' GenericSort Macro
'
Range("A6:QS100").Select
Range("H6").Activate
ActiveWorkbook.Worksheets("Summary").Sort.SortFields.Clear
ActiveWorkbook.Worksheets("Summary").Sort.SortFields.Add Key:=Range("H6:H100") _
, SortOn:=xlSortOnValues, Order:=xlDescending, DataOption:=xlSortNormal
ActiveWorkbook.Worksheets("Summary").Sort.SortFields.Add Key:=Range("D6:D100") _
, SortOn:=xlSortOnValues, Order:=xlDescending, DataOption:=xlSortNormal
ActiveWorkbook.Worksheets("Summary").Sort.SortFields.Add Key:=Range("C6:C100") _
, SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
With ActiveWorkbook.Worksheets("Summary").Sort
.SetRange Range("A6:QS100")
.Header = xlGuess
.MatchCase = False
.Orientation = xlTopToBottom
.SortMethod = xlPinYin
.Apply
End With
Range("A6").Select
End Sub