Auto Hyperlink to newest worksheet in workbook?

[lunatyk05]

Hello Everyone, I have a problem and it seems like this is definitely the forum to find the help I need.

I have a workbook with several worksheets that I use to manage files. I use this workbook to keep track of a certain type of file as well as quick refernce information. Each file is basically given a profile in the form of a separate worksheet. With each new file a new worksheet is created and their information is entered.

Specifically my workbook contains worksheets named "names," "allowance," and "template." In the "template" cell C2 is the name of the file. I have been successful so far in creating a macro that will copy all of the information from the "template," it then creates a new worksheet and renames that worksheet with the name from cell C2. The Macro then takes the same name from C2 and enters it into cell B10 on the "names" worsksheet, then arranges all file names in the "names" workssheet alphabetically so that all new files are always arranged alphabetically. I cannot seem to get the macro to hyperlink correctly for me though.

What I would like for it to be able to do is for the name that has just been pasted into cell B10 of the "names" worksheet, I would like it to auto hyperlink to the worksheet that was just created for that file right before it arranges alphabetically. Or put another way, I would like the macro to paste the name into cell B10, hyperlink to the worksheet that was just created for that file, and then arrange the data alphabetically.

My problem lies here: (I've used "Test Name" as the name)
ActiveSheet.Hyperlinks.Add Anchor:=Selection, Address:="", SubAddress:= _
"'Test Name'!A1", TextToDisplay:="Test Name"
I created this code by turning on the Macro recorder, selecting the box to hyperlink to, selcting add hyperlink, then selecting the correct worksheet.

With this, I end up with every new name linking to the same worksheet instead of the correct worksheet. I need the SubAddress to link to either the newest worksheet or the worksheet that matches the name in the box. I also need the TextToDisplay to just stay as what is already in the box.

Sorry if this is not clear, please let me know all of your questions,

Thanks!