Sorting column alphabetically by surname

I've already got this code on to sort a column alphabetically so that whenever I add a name to the bottom of the list, the column re-sorts itself to accommodate the new name..

Please Login or Register  to view this content.

What I'm asking is if there is a way to edit this code so that the column sorts by the surname and not the first name?

I've seen methods that involve a column of names that will never change, but my list is constantly changing by adding new names in and deleting old names

Thanks for any help

Originally Posted by Arkadi

typically Range("A1") would be changed to encompass the whole range, and the Key1 parameter of your sort is the column to sort by, so if you change the range, and change the key to B2 instead of A2, then it will sort by the surname (assuming surname is in column B). If I assume you have data in columns A through D for example, then:

Please Login or Register  to view this content.

You could specify a specific range, but using Columns is easier since you don't have to first calculate the last row.

Thanks for the reply.

Sorry I should have said that the names are all in one column to satisfy a lookup i have that matches the name with a number.

I just wasn't sure whether it was possible, with the name in one cell, to sort by the surname instead of what it understandably already does and sorts by the first letter in the cell.

I'm currently working around it by changing the names in the lookup to "Surname, First name", but would obviously prefer it to be "FirstName Surname"

Originally Posted by Arkadi

Ok so columns ABC are in one workbook, but you want to sort the OTHER workbook?

Yes that's right. The workbook with all of the names and number isn't mine. But I can sort it any way that will make my own workbook how I want it

I have my own section of workers (and so I don't need all of the names, just the ones i type into my own workbook) and so use the workbook to populate my own workbook

Originally Posted by Arkadi

Well I'm a bit confused, but if I understand right:
Workbook A, which has separate first name last name and employee number, is used to genereate the initial data, which is placed in workbook B?
Then workbook B becomes independent and you want to sort it, whenever you add new names?

Yes workbook A had FirstName, Surname and employee number all in different columns. I've used "CONCATENATE" in another column in this workbook to join the names in the format "Surname, FirstName". But the end goal is to change the format to "FirstName Surname" and be able to sort alphabetically on workbook B by surname.

On workbook B (the one I want sorting by surname) I type in the names of the employees in column A (in "Surname, FirstName" format) and the employee number in column B is filled using a lookup to workbook A. So at the moment the code I have all works fine by auto-sorting the names and employees numbers alphabetically (as I have changed the format so Surname is first). But I'm looking to have this happen, but being able to change the format I type the names in with to "FirstName Surname"

Hope this makes sense

Originally Posted by Arkadi

So on workbook B, if you are using a lookup to populate B with the emp. Number, could you not do a lookup in column C as well, but return the surname instead of employee number?
You could hide column C so it won't actually show up, and you could make your sort use C2 instead of A2 as the Key1 value in the sort. Just adjust your sort to:

Please Login or Register  to view this content.

I think I understand. I've done the look up to get the last name only in C, but putting in..

Please Login or Register  to view this content.

doesn't seem to work any more to auto-sort the column

Sorry no, ignore me.

It works perfectly, thanks for your help, reputation added!