Am Tue, 8 Jul 2014 11:08:29 -0700 (PDT) schrieb krio_gyal:

Hi, I'm relatively inexperienced when it comes to macros. I usually record a macro and then edit to do what i want, but I'm having trouble with this one. Here's what i recorded:

ub Sort_OverUnder()
'
' Sort_OverUnder Macro
' Sort lines for Over/Under Report
'
' Keyboard Shortcut: Ctrl+q
'
Range("B6:N41").Select
ActiveWorkbook.Worksheets("Over_Under_Dept").Sort. SortFields.Clear
ActiveWorkbook.Worksheets("Over_Under_Dept").Sort. SortFields.Add Key:=Range( _
"F7:F41"), SortOn:=xlSortOnValues, Order:=xlDescending, DataOption:= _
xlSortNormal
With ActiveWorkbook.Worksheets("Over_Under_Dept").Sort
.SetRange Range("B6:N41")
.Header = xlYes
.MatchCase = False
.Orientation = xlTopToBottom
.SortMethod = xlPinYin
.Apply
End With
Range("B7:N21").Select
ActiveWorkbook.Worksheets("Over_Under_Dept").Sort. SortFields.Clear
ActiveWorkbook.Worksheets("Over_Under_Dept").Sort. SortFields.Add Key:=Range( _
"E7:E21"), SortOn:=xlSortOnValues, Order:=xlDescending, DataOption:= _
xlSortNormal
With ActiveWorkbook.Worksheets("Over_Under_Dept").Sort
.SetRange Range("B6:N21")
.Header = xlYes
.MatchCase = False
.Orientation = xlTopToBottom
.SortMethod = xlPinYin
.Apply
End With
Range("B23:N41").Select
ActiveWorkbook.Worksheets("Over_Under_Dept").Sort. SortFields.Clear
ActiveWorkbook.Worksheets("Over_Under_Dept").Sort. SortFields.Add Key:=Range( _
"E23:E41"), SortOn:=xlSortOnValues, Order:=xlDescending, DataOption:= _
xlSortNormal
With ActiveWorkbook.Worksheets("Over_Under_Dept").Sort
.SetRange Range("B23:N41")
.Header = xlGuess
.MatchCase = False
.Orientation = xlTopToBottom
.SortMethod = xlPinYin
.Apply
End With
Range("I22").Select
End Sub

The problem is where it says " Range("B7:N21").Select" and "Range("B23:N41").Select". With the first sort, the "Total" row ended up in row 22. Now I need the next sort to first sort everything above the Total row and then everything below it, keeping in mind, that depending on the data which changes weekly, the Total row could end up being any row after the first sort. Is there a way to do this?

Thanks.


Regards
Claus B.
--
Vista Ultimate / Windows7
Office 2007 Ultimate / 2010 Professional