Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Sorting after subtotal
My user wants the subtotals in a specific order --- which is not
alphabetical. She has numbered the order the subtotals should appear. Any other subtotal groups will follow the listed subtotal groups. Note: these groups may or may not always exists in a report. 1. Cash 2. Bonds 3. Common Stock 4. Common Funds 5. Other category 6. Other category...... '----------------------------------------------------------- Sort ' Range("A1:I1").Select ' ActiveCell.SpecialCells(xlLastCell).Select ' Range("A1").Select ' Range(Selection, ActiveCell.SpecialCells(xlLastCell)).Select ' Selection.Sort Key1:=Range("A4"), Order1:=xlAscending, Key2:=Range("C4") _ ' , Order2:=xlAscending, Key3:=Range("B4"), Order3:=xlAscending, Header:= _ ' xlGuess, OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _ ' DataOption1:=xlSortNormal, DataOption2:=xlSortNormal, DataOption3:= _ ' xlSortNormal ''----------------------------------------------------------- SubTotal ' Selection.Subtotal GroupBy:=1, Function:=xlSum, TotalList:=Array(3, 4, 5, 6, 7, _ ' 8, 9), Replace:=True, PageBreaks:=False, SummaryBelowData:=True ' Range("A1:I1").Select ' ActiveWorkbook.Save |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Sorting after subtotal
Just a suggestion.
Put a macro in that will do a sort for you. If you label the cells like this. 1 Cash 2 Bonds 3 Common etc, the 'sort data' will use the numbers to sort so it will always put the reults in any order you number them. If a group is not in the report (such as 2 Bonds) sort data will look for the next chronological number. "SharonInGa" wrote: My user wants the subtotals in a specific order --- which is not alphabetical. She has numbered the order the subtotals should appear. Any other subtotal groups will follow the listed subtotal groups. Note: these groups may or may not always exists in a report. 1. Cash 2. Bonds 3. Common Stock 4. Common Funds 5. Other category 6. Other category...... '----------------------------------------------------------- Sort ' Range("A1:I1").Select ' ActiveCell.SpecialCells(xlLastCell).Select ' Range("A1").Select ' Range(Selection, ActiveCell.SpecialCells(xlLastCell)).Select ' Selection.Sort Key1:=Range("A4"), Order1:=xlAscending, Key2:=Range("C4") _ ' , Order2:=xlAscending, Key3:=Range("B4"), Order3:=xlAscending, Header:= _ ' xlGuess, OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _ ' DataOption1:=xlSortNormal, DataOption2:=xlSortNormal, DataOption3:= _ ' xlSortNormal ''----------------------------------------------------------- SubTotal ' Selection.Subtotal GroupBy:=1, Function:=xlSum, TotalList:=Array(3, 4, 5, 6, 7, _ ' 8, 9), Replace:=True, PageBreaks:=False, SummaryBelowData:=True ' Range("A1:I1").Select ' ActiveWorkbook.Save |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Sorting after subtotal
The raw data I am manipulating is from an email. My task is to write a
macro formatting this data to a final report that the users do not have to manipulate. How do you sort using variables? "SharonInGa" wrote: My user wants the subtotals in a specific order --- which is not alphabetical. She has numbered the order the subtotals should appear. Any other subtotal groups will follow the listed subtotal groups. Note: these groups may or may not always exists in a report. 1. Cash 2. Bonds 3. Common Stock 4. Common Funds 5. Other category 6. Other category...... '----------------------------------------------------------- Sort ' Range("A1:I1").Select ' ActiveCell.SpecialCells(xlLastCell).Select ' Range("A1").Select ' Range(Selection, ActiveCell.SpecialCells(xlLastCell)).Select ' Selection.Sort Key1:=Range("A4"), Order1:=xlAscending, Key2:=Range("C4") _ ' , Order2:=xlAscending, Key3:=Range("B4"), Order3:=xlAscending, Header:= _ ' xlGuess, OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _ ' DataOption1:=xlSortNormal, DataOption2:=xlSortNormal, DataOption3:= _ ' xlSortNormal ''----------------------------------------------------------- SubTotal ' Selection.Subtotal GroupBy:=1, Function:=xlSum, TotalList:=Array(3, 4, 5, 6, 7, _ ' 8, 9), Replace:=True, PageBreaks:=False, SummaryBelowData:=True ' Range("A1:I1").Select ' ActiveWorkbook.Save |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Sorting and subtotal in a filtered list | Excel Discussion (Misc queries) | |||
Sorting a subtotal list by the subtotals | Excel Discussion (Misc queries) | |||
Subtotal sorting | Excel Worksheet Functions | |||
Sorting subtotal results | New Users to Excel | |||
Subtotal results in new column and then sorting subtotals | Excel Worksheet Functions |