sorting a changing range
 

I have been working on this for a while with no luck. I have a list in 3
different sheets for different departments. I am using a userform to enter
the new data and i need some way to set the new range to sort when new data
is added. i need to sort using column b and d.

for example
a b c d e f
1 Charlie 7-27-07 sick
2 Zach 8-21-07 late
3 charlie 1-30-07 off
4 Steve 9-02-07 late


this is what i have but
when a different department (that goes on a different sheet) is selected it
does not work.

if you want to look at the entire code i can post.

Dim NumCount As Long
NumCount = Cells(Rows.Count, "b").End(xlUp).Row
Range("b5:f" & NumCount).sort Key1:=Range("b5"), key2:=Range("d5")