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

howardgrigg

New Member
Joined
Oct 10, 2006
Messages
2
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.
 

Excel Facts

Copy a format multiple times
Select a formatted range. Double-click the Format Painter (left side of Home tab). You can paste formatting multiple times. Esc to stop

SydneyGeek

MrExcel MVP
Joined
Aug 5, 2003
Messages
12,251
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...
Code:
    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
 

Forum statistics

Threads
1,140,913
Messages
5,703,131
Members
421,277
Latest member
abudgen

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top