seting a changing range to sort on 3 different sheets

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

jhyatt - 18 Sep 2007 15:34 GMT

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

Reply to this Message

pablo bellissimo - 18 Sep 2007 16:20 GMT

If your userform is adding the new data in the first available row beneath
the existing data could you just add the following at the end of the code you
are using to add the data to each worksheet?

Range("A2").Select
Selection.CurrentRegion.Select
Selection.Sort Key1:=Range("B2"), Key2:=Range("D2")

?? does this help?

> 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
[quoted text clipped - 7 lines]
> 3 charlie 1-30-07 off
> 4 Steve 9-02-07 late

Reply to this Message

jhyatt - 18 Sep 2007 16:40 GMT

the code you gave does not sort to the existing list 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")

> If your userform is adding the new data in the first available row beneath
> the existing data could you just add the following at the end of the code you
[quoted text clipped - 17 lines]
> > 3 charlie 1-30-07 off
> > 4 Steve 9-02-07 late

Reply to this Message