Sort automatically?

[RickeyS]

I have a spreadsheet that keeps track of overtime hrs worked, which based on person with lowest hrs I can assign the next overtime to the person with the lowest hrs. When I put in hours worked I have a column that keeps a running total.
Here's what I need:
Once I enter someone's hours and it auto sums I want the sheet to automatically resort for lowest hours (running total). How would I do this?
Thanks
Rick

 

[dave3009]

Hi Rick

You could try this in the sheet module for the sheet in question

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Application.ScreenUpdating = True

    Range("C2:C50").Sort Key1:=Range("C2"), Order1:=xlAscending, Header:= _
        xlGuess, OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _
        DataOption1:=xlSortNormal

End Sub

Change the C2:C50 to your range and C2 to your sort start point

KR

Dave

EDIT: the C2:C50 will need to relate to the entire range you want to sort so for example if your total was in C and your entery points were A and B it would read Range("A2:C50"). Sorry for got to clarify
[/code]

 

[RickeyS]

Okay I got this far and it works, but it will update the sheet if any cell has been changed and I would like it to update only if the following cells have changed (F8:S28 or F34:S50) What do I need to do?

Private Sub Worksheet_Change(ByVal Target As Range)
Range("A8:T28").Select
Selection.Sort Key1:=Range("T8"), Order1:=xlAscending, Key2:=Range("C8") _
, Order2:=xlAscending, Header:=xlGuess, OrderCustom:=1, MatchCase:= _
False, Orientation:=xlTopToBottom, DataOption1:=xlSortNormal, DataOption2 _
:=xlSortNormal
Range("A34:T50").Select
Selection.Sort Key1:=Range("T34"), Order1:=xlAscending, Key2:=Range("C34" _
), Order2:=xlAscending, Header:=xlGuess, OrderCustom:=1, MatchCase:= _
False, Orientation:=xlTopToBottom, DataOption1:=xlSortNormal, DataOption2 _
:=xlSortNormal
Range("F8").Select
End Sub


Thanks
Rick

 

[Smitty]

How's this (not tested):

<font face=Tahoma><SPAN style="color:#00007F">Private</SPAN> <SPAN style="color:#00007F">Sub</SPAN> Worksheet_Change(<SPAN style="color:#00007F">ByVal</SPAN> Target <SPAN style="color:#00007F">As</SPAN> Range)
    <SPAN style="color:#00007F">Dim</SPAN> rng <SPAN style="color:#00007F">As</SPAN> Range
         <SPAN style="color:#007F00">'   Only look at single cell changes</SPAN>
        <SPAN style="color:#00007F">If</SPAN> Target.Count > 1 <SPAN style="color:#00007F">Then</SPAN> <SPAN style="color:#00007F">Exit</SPAN> <SPAN style="color:#00007F">Sub</SPAN>
        <SPAN style="color:#007F00">'   Set Target Range</SPAN>
        Set rng = Range("F8:S28, F34:S50")
        <SPAN style="color:#007F00">'   Only look at that range</SPAN>
        <SPAN style="color:#00007F">If</SPAN> Intersect(Target, rng) <SPAN style="color:#00007F">Is</SPAN> <SPAN style="color:#00007F">Nothing</SPAN> <SPAN style="color:#00007F">Then</SPAN> <SPAN style="color:#00007F">Exit</SPAN> <SPAN style="color:#00007F">Sub</SPAN>
            <SPAN style="color:#007F00">'   Action if Condition(s) are met</SPAN>
            Range("A8:T28").Sort Key1:=Range("T8"), Order1:=xlAscending, Key2:=Range("C8") _
                , Order2:=xlAscending, Header:=xlGuess, OrderCustom:=1, MatchCase:= _
                <SPAN style="color:#00007F">False</SPAN>, Orientation:=xlTopToBottom, DataOption1:=xlSortNormal, DataOption2 _
                :=xlSortNormal
            Range("A34:T50").Sort Key1:=Range("T34"), Order1:=xlAscending, Key2:=Range("C34" _
                ), Order2:=xlAscending, Header:=xlGuess, OrderCustom:=1, MatchCase:= _
                <SPAN style="color:#00007F">False</SPAN>, Orientation:=xlTopToBottom, DataOption1:=xlSortNormal, DataOption2 _
                :=xlSortNormal
<SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">Sub</SPAN></FONT>

HTH,

Smitty

 

[RickeyS]

Thanks Smitty, Works great.

Thanks again,
Rick

 

[KWD]

Smity,

I am trying to do a very similar thing, only sorting when a change is made to a certain range. Except, I don't make the change to the range to be sorted directly, it is by formula which references another worksheet. (i.e I wish to sort a range in worksheet A. Worksheet A has a formula which references worksheet b, when I make a change in worksheet b thus changing the cells in worksheet a, your code as written does not activate and sort it). Does that make sence?

You code does not seem to "see this change" and thereby "auto sort". Any thought on how to change this to make it work?

 

[Smitty]

If you want to trigger an event based on a formula calculating, you need to use a sheet calculate evnt, which does not support the Target argument.

I.E.

Private Sub Worksheet_Calculate()
  '  Do something
End Sub

If you give more details someone can start on an answer.

Smitty

 

[KWD]

Here's a quick sample of the sheet I am trying to sort (if this is not clear I'll post the sheet).

I want to sort range c5:F21 (sort decending on column f). Here are sample values (these are standings for a football pool)

c5=RANK(F5,$F$5:$F$21)&'Week 1'!$N$1
d5='Pro Playoffs'!$N$22
Column E is empty
f5=IF(LEFT(D5,FIND("-",D5)-1)="0",0,LEFT(D5,FIND("-",D5)-1)/(LEFT(D5,FIND("-",D5)-1)+RIGHT(D5,LEN(D5)-(FIND("-",D5))))) (is a %)

Column c is a players rank and name (looks like "1 Kevin")
Column d is players record (looks like 99-18)
Column f is the players pick percentage

The ramining cells are similar, they reference the same worksheets just different cells. Columns D and F will be the ones that change. Is this enough to go on? I'll want to sort other ranges on this sheet, but if we can get one working I hope I can figure out the rest.

Thanks

 

[Smitty]

Here's a start:

<font face=Tahoma><SPAN style="color:#00007F">Private</SPAN> <SPAN style="color:#00007F">Sub</SPAN> Worksheet_Calculate()
    Range("C5:F21").Sort Key1:=Range("C5"), Order1:=xlAscending, Header:= _
        xlGuess, OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _
        DataOption1:=xlSortTextAsNumbers
<SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">Sub</SPAN>
</FONT>

Note that I use Excel 2003, which has expanded sort options from prior versions, so it'll likely bomb in 2000 or earlier. In which case, just record your sort, then append the code.

Smitty

 

[KWD]

Thanks that does work!

One problem. When I make a change that changes the sort range, excel now appears to be in a endless loop "Calculating cells". It gets up to about 80%, then starts again at 0%. The sort completes, but maybe there is some cyclic reference?

Thanks for the continuing help