Excel VBA question sorting data twice

[ageffre]

Hello,

I would like to be able to sort a priority list using VBA. I would like to sort first by priority override, simple number sort that trumps anything else. Next I would like to do a "combination" sort. First sort on absolute need date, then on priority: high, medium, low. Then I would like to come back and sort on target date, then again on the priority for those tasks by high, medium, low. I tried some VBA code, but it isn't working at all.

Sub Sorting()


Range("A1:H200").Select


ActiveWorkbook.Worksheets("Active").Sort.SortFields.Clear


' This sorts by Priority Override First
ActiveWorkbook.Worksheets("Active").Sort.SortFields.Add Key:=Range("A1"), SortOn:=xlSortOnValues, _
Order:=xlAscending, DataOption:=xlSortNormal


'This sorts by Absolute Need Date Second
ActiveWorkbook.Worksheets("Active").Sort.SortFields.Add Key:=Range("E1"), Order:=xlAscending, _
DataOption:=xlSortNormal


'This sorts by Priority
ActiveWorkbook.Worksheets("Active").Sort.SortFields.Add Key:=Range("B1"), SortOn:=xlSortOnValues, _
Order:=xlAscending, CustomOrder:="High,Medium,Low", DataOption:=xlSortNormal


'This sorts by Target Date Third
ActiveWorkbook.Worksheets("Active").Sort.SortFields.Add Key:=Range("D1"), SortOn:=xlSortOnValues, _
Order:=xlAscending, DataOption:=xlSortNormal


'This sorts by Priority
ActiveWorkbook.Worksheets("Active").Sort.SortFields.Add Key:=Range("B1"), SortOn:=xlSortOnValues, _
Order:=xlAscending, CustomOrder:="High,Medium,Low", DataOption:=xlSortNormal


With ActiveWorkbook.Worksheets("Active").Sort


.SetRange Range("A1:H200")
.Header = xlYes
.MatchCase = False
.Orientation = xlTopToBottom
.SortMethod = xlPinYin
.Apply


End With


Range("A1").Select


End Sub

 

[rlv01]

1. What do you mean by "but it isn't working at all"?
2. Is this supposed to be one big sort operation on Range("A1:H200"), or three different, distinct sort operations?

 

[ageffre]

Hi,

1. I must have had something else going on, because the code does complete the sorting. Not quite what I am after though.
2. It should act as one big sort operation on the entire range you mentioned. I would like it to sort what I guess you could call distinct operations. The code does complete the first function (sorting on the override column). Then it does also sort by absolute need date and even then sorts by priority if several of the absolute dates are the same. What it doesn't do is then sort by target date and organize the target dates (if some are common) by low, medium, high priority. The code as is essentially stops after the custom priority and does nothing with the target date data.

 

[rlv01]

I think that's because you're setting up a bunch of sort keys and then doing the sort on a single range of cells.

If you define a sort as (for example):

First by Column A
then by column B
then by column C
then by column D
then by column E
then by column F​

And then execute it, It is probably not going to matter what is in column F as everything about the final sorted order will have likely been determined by the first two or three columns, particularly if column A is a unique priority identifier

I think after the first big sort that does the whole range, you are going to have to do some smaller sorts on some sub-ranges of A1:H200 that are grouped according to the results of the big overall sort. I'd say first do it manually using the sort menu and pay attention to what cell ranges you need to select to achieve the result you want. That will give you an idea of how to approach automating the sort.

 

[ageffre]

I think you are right. The simple code was trying to do too much at once. I will break it down into manageable pieces. Thank you for the help and the quick responses.