+ Reply to Thread
Results 1 to 6 of 6

Alphabetize when data changes

  1. #1
    Chris
    Guest

    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.

  2. #2
    Forum Expert Simon Lloyd's Avatar
    Join Date
    03-02-2004
    Location
    locked in the cage
    MS-Off Ver
    All the ones my homepage shows
    Posts
    3,161
    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

    ActiveCell.SpecialCells(xlLastCell).Select
    End If
    End Sub
    Last edited by Simon Lloyd; 01-09-2006 at 10:42 AM.

  3. #3
    Chris
    Guest

    Re: 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
    >
    >


  4. #4
    Tom Ogilvy
    Guest

    Re: Alphabetize when data changes

    how about changing

    Columns("A:A").Select

    to

    Columns("A:B").Select

    --
    Regards,
    Tom Ogilvy


    "Chris" <[email protected]> wrote in message
    news:[email protected]...
    > 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
    > >
    > >




  5. #5
    Chris
    Guest

    Re: 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" <[email protected]> wrote in message
    > news:[email protected]...
    > > 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
    > > >
    > > >

    >
    >
    >


  6. #6
    Pat
    Guest

    Re: 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
    >
    >


+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1