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

[howardgrigg]

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.

 

[SydneyGeek]

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.

Denis