Alphabetize when data changes
 

Hello,
I have a workbook that has a number of pages. Each page contains a list of
roughly 30 students. I want to have one master sheet that lists all the
students in alphabetical order. Right now I have a formula that pulls the
names from each name slot on each of the pages as well as a number showing
which page. However, I would like it to automatically realphabetize whenever
I add an additional name. I dont know if this is possible, but it would
really help because the teacher I am doing it for is fairly illiterate when
it comes to computers and would probably mess it up.

Alphabetize when data changes
 

Try this, it re-alphabetises from cell A1 when a new entry is added,
paste this code directly on to the code sheet for sheet you want this
to happen on.

Regards
Simon

Private Sub Worksheet_Change(ByVal Target As Range)
If ActiveCell.Column = 2 Then 'This tells the sub not to work if the
column is number 2 or more
Exit Sub
ElseIf ActiveCell.Column = 1 Then 'This tells the sub to only work if
column 1
Columns("A:A").Select 'This is your column range and the A1 below is
telling the sort where to start
Selection.Sort Key1:=Range("A1"), Order1:=xlAscending,
Header:=xlGuess, _
OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom,
_
DataOption1:=xlSortNormal

End If
End Sub


--
Simon Lloyd
------------------------------------------------------------------------
Simon Lloyd's Profile: http://www.excelforum.com/member.php...fo&userid=6708
View this thread: http://www.excelforum.com/showthread...hreadid=499263

Alphabetize when data changes
 

Thanks for the suggestion, however, I need it to include the contents of B1
with A1, because it is in B1 that I have which class # the name in A1 is in.
Is there any quick way to change the code to sort A1 alphabetically include
B1. Hopefully I am being clear enough.

Thanks!

"Simon Lloyd" wrote:


Try this, it re-alphabetises from cell A1 when a new entry is added,
paste this code directly on to the code sheet for sheet you want this
to happen on.

Regards
Simon

Private Sub Worksheet_Change(ByVal Target As Range)
If ActiveCell.Column = 2 Then 'This tells the sub not to work if the
column is number 2 or more
Exit Sub
ElseIf ActiveCell.Column = 1 Then 'This tells the sub to only work if
column 1
Columns("A:A").Select 'This is your column range and the A1 below is
telling the sort where to start
Selection.Sort Key1:=Range("A1"), Order1:=xlAscending,
Header:=xlGuess, _
OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom,
_
DataOption1:=xlSortNormal

End If
End Sub


--
Simon Lloyd
------------------------------------------------------------------------
Simon Lloyd's Profile: http://www.excelforum.com/member.php...fo&userid=6708
View this thread: http://www.excelforum.com/showthread...hreadid=499263

Alphabetize when data changes
 

how about changing

Columns("A:A").Select

to

Columns("A:B").Select

--
Regards,
Tom Ogilvy


"Chris" wrote in message
...
Thanks for the suggestion, however, I need it to include the contents of
B1
with A1, because it is in B1 that I have which class # the name in A1 is
in.
Is there any quick way to change the code to sort A1 alphabetically
include
B1. Hopefully I am being clear enough.

Thanks!

"Simon Lloyd" wrote:


Try this, it re-alphabetises from cell A1 when a new entry is added,
paste this code directly on to the code sheet for sheet you want this
to happen on.

Regards
Simon

Private Sub Worksheet_Change(ByVal Target As Range)
If ActiveCell.Column = 2 Then 'This tells the sub not to work if the
column is number 2 or more
Exit Sub
ElseIf ActiveCell.Column = 1 Then 'This tells the sub to only work if
column 1
Columns("A:A").Select 'This is your column range and the A1 below is
telling the sort where to start
Selection.Sort Key1:=Range("A1"), Order1:=xlAscending,
Header:=xlGuess, _
OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom,
_
DataOption1:=xlSortNormal

End If
End Sub


--
Simon Lloyd
------------------------------------------------------------------------
Simon Lloyd's Profile:
http://www.excelforum.com/member.php...fo&userid=6708
View this thread:
http://www.excelforum.com/showthread...hreadid=499263

Alphabetize when data changes
 

Duh, I should have noticed that. It does work perfectly now! Thanks a lot!

"Tom Ogilvy" wrote:

how about changing

Columns("A:A").Select

to

Columns("A:B").Select

--
Regards,
Tom Ogilvy


"Chris" wrote in message
...
Thanks for the suggestion, however, I need it to include the contents of
B1
with A1, because it is in B1 that I have which class # the name in A1 is
in.
Is there any quick way to change the code to sort A1 alphabetically
include
B1. Hopefully I am being clear enough.

Thanks!

"Simon Lloyd" wrote:


Try this, it re-alphabetises from cell A1 when a new entry is added,
paste this code directly on to the code sheet for sheet you want this
to happen on.

Regards
Simon

Private Sub Worksheet_Change(ByVal Target As Range)
If ActiveCell.Column = 2 Then 'This tells the sub not to work if the
column is number 2 or more
Exit Sub
ElseIf ActiveCell.Column = 1 Then 'This tells the sub to only work if
column 1
Columns("A:A").Select 'This is your column range and the A1 below is
telling the sort where to start
Selection.Sort Key1:=Range("A1"), Order1:=xlAscending,
Header:=xlGuess, _
OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom,
_
DataOption1:=xlSortNormal

End If
End Sub


--
Simon Lloyd
------------------------------------------------------------------------
Simon Lloyd's Profile:
http://www.excelforum.com/member.php...fo&userid=6708
View this thread:
http://www.excelforum.com/showthread...hreadid=499263

Alphabetize when data changes
 

"Simon Lloyd" wrote:


Try this, it re-alphabetises from cell A1 when a new entry is added,
paste this code directly on to the code sheet for sheet you want this
to happen on.

Regards
Simon

Private Sub Worksheet_Change(ByVal Target As Range)
If ActiveCell.Column = 2 Then 'This tells the sub not to work if the
column is number 2 or more
Exit Sub
ElseIf ActiveCell.Column = 1 Then 'This tells the sub to only work if
column 1
Columns("A:A").Select 'This is your column range and the A1 below is
telling the sort where to start
Selection.Sort Key1:=Range("A1"), Order1:=xlAscending,
Header:=xlGuess, _
OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom,
_
DataOption1:=xlSortNormal

End If
End Sub


--
Simon Lloyd
------------------------------------------------------------------------
Simon Lloyd's Profile: http://www.excelforum.com/member.php...fo&userid=6708
View this thread: http://www.excelforum.com/showthread...hreadid=499263