Thread: Automatically Sorting a referenced sheet

You've only referred to two sheets but your upload is massive, help us to help you by being a bit more specific....

You can solve your problem by inserting a sort method into the sheet_change event - you'd probably also be best off using a dynamic named range for it too. I can show you if you could post a smaller example

CC

Thank you, I will try to figure this out. When you say put this into any of the modules, do you mean I can click on any of the 4 sheets and select the VBA options and paste it in there? Where exactly does the worksheet code go. I'm sorry, but I am a somewhat novice user when it comes to certain aspects of excel. And when you say changethe SORT PRIORITY are you referring to be able to sort it by Name, room, ph, etc... well that would be fantastic if thats true. Thank you for your help.

Hi, two things, this is a better implementation (workbook-level event):

Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)
    Select Case Sh.Name
    Case "1st", "2nd", "3rd"
        Sheets("Alpha Roster").Range(Sheets("Alpha Roster").Range("A3"), Sheets("Alpha Roster").Range("C3").End(xlDown)).Sort _
        Key1:=Sheets("Alpha Roster").Range("A3"), _
        Order1:=xlAscending, _
        DataOption1:=xlSortNormal, _
        Key2:=Sheets("Alpha Roster").Range("B3"), _
        Order2:=xlAscending, _
        DataOption2:=xlSortNormal, _
        Key3:=Sheets("Alpha Roster").Range("C3"), _
        DataOption3:=xlSortNormal, _
        Order3:=xlAscending, _
        Header:=xlNo, _
        OrderCustom:=1, _
        MatchCase:=False, _
        Orientation:=xlTopToBottom
    End Select
End Sub

2nd, I thought you had some understanding of VBA from the contents of the workbook, but never mind...
This code needs to go in the workbook code:
get there by pressing Alt+F11
on the left of the window that pops up there should be a project explorer (looks like folder tree)
find your workbook (looks like VBAPROJECT (NEW ALL ROOMS...)
open it with the + if not already open
open Microsoft Excel Objects folder if not already open
Double click "ThisWorkbook"
Paste code into here

Editing the functionality:
Sheets which effect a sort:
A line begins "Case sh.name", put the names of any sheet whose changes you want to force a sort of alpha roster into that list
Sort priority:
Three lines begin "Key" (1,2, & 3) change which one is A, B or C to define the sort priority.

HTH

Ok, I think I did exactly what you said when it came to the VBA portion, but it's still not sorting it. I did not wuite understand the later hald of the sorting part. I ahve attached the modified copy with the VBA embedded into it like you suggested.

You're missing the last line... "end sub"

Sir, I tried to make the corrections, but I am still coming up with the same results. I ahve attached the altered copy. Please advise.

This works for me, check the Alpha roster page, then change something in sheets 1st, 2nd or 3rd then check the Alpha roster page again.

CC

No sir, it's still not working. I am using Office 2007 FYU. I added my name SOUZA, and it is not even showing on the Alpha Roster, additionally if you notice the pics, the names are not in Alpha order? Wierd

It does work



If you want to change the sort priority then do so by swapping "A3", "B3", and "C3" which you can see on lines 5, 8 and 11 of the solution given. You may want the order "B3", "A3", "C3".

I have no idea what you want.

CC

On my Alpha Roster, I want all the names of everyone in the Dorm (1st, 2nd, 3rd) in Alphabetical Order. That's pretty much all I want.

Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)
    Select Case Sh.Name
    Case "1st", "2nd", "3rd"
        Sheets("Alpha Roster").Range(Sheets("Alpha Roster").Range("A3"), Sheets("Alpha Roster").Range("C3").End(xlDown)).Sort _
        Key1:=Sheets("Alpha Roster").Range("B3"), _
        Order1:=xlAscending, _
        DataOption1:=xlSortNormal, _
        Key2:=Sheets("Alpha Roster").Range("A3"), _
        Order2:=xlAscending, _
        DataOption2:=xlSortNormal, _
        Key3:=Sheets("Alpha Roster").Range("C3"), _
        DataOption3:=xlSortNormal, _
        Order3:=xlAscending, _
        Header:=xlNo, _
        OrderCustom:=1, _
        MatchCase:=False, _
        Orientation:=xlTopToBottom
    End Select
End Sub

See how I've changed it to B3, A3, C3?
You didn't qualify your sort order when asked and you didn't explain why it wasn't working - you just said "it's not working". How can I help with feedback like that?!?!?!

/thread

Sir, I'm sorry I have made you fustrated with my lack of clarity. You have been a great help, and all your help has helped me clear some things up. Thank you again and I'll be closing this post. You were a great help.

Don't worry - it's hard to explain when you don't know why it doesn't work... that doesn't make it easy to understand either though...

CC