A macro to insert a Name into a column in alphabetical order


New Member
Oct 10, 2006
I have setup a reasonably complicated document but am struggaling with the final thing that it needs.

How it works at the moment is that it you can enter onto the front sheet into a field, a name and hit a submit button. The macro then creates a new sheet with the name that was entered and formats it how I have setup then clears the field where it was entered on the front sheet ready for the next time someone wants to add a page.

However what I also want it to do is to put the name that was entered in the right place in an alphabetical list on the front sheet as a link to the sheet that it corrosponds to. Still following? So at the end, the front sheet has an up to date list in alphabetical order linking to the sheets in the rest of the document.

If that is possible it would be nice if at the top of the list you could have links for 'A' and 'B' and 'C' etc so that you could just click on a letter and that scrolls down to the point on the list where that letter begins, like clicking on 'P' would take you to the frontpage list exactly where the entrys starting with 'P' start.

Thanks for any advice in advance and I will be happy to provide more info if it is needed.

Some videos you may like

Excel Facts

Select a hidden cell
Somehide hide payroll data in column G? Press F5. Type G1. Enter. Look in formula bar while you arrow down through G.


MrExcel MVP
Aug 5, 2003
Hi Howard, welcome to the Board!

A suggestion:
Assuming that you are adding the link to the new sheet at the bottom of the list, all you need to do is add a Sort routine at the end of the code. It's a lot easier than trying to work out just where to insert the new name.

I have used code like this to sort indexes before...
    Application.Goto reference:="Projects_LU"
    Selection.Sort Key1:=Range("C4"), Order1:=xlAscending, Header:=xlGuess, _
        OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom
This sorts using column C as the sort field (C4 is the top of the table) and not using headers. Projects_LU is a dynamic range containing the items to be sorted.


Watch MrExcel Video

Forum statistics

Latest member