Macro to create sheet, rename sheet, and hyperlink to it

Hi,

I have no experience with VBA or macros, and am doing my best to figure it out. I am trying to streamline a master spreadsheet we use for registering people for classes by creating a separate sheet for each person on which we can record their payment history without cluttering the master spreadsheet. Working from the master registration spreadsheet, I would like to do the following:

Add a new person on the next row/line, then run a macro that will create a new tab/worksheet, name the tab after that person (referencing the cell with their last name), copy the $ amount due from the master spreadsheet into their personal tab/worksheet, then create a hyperlink from the master spreadsheet to the new one, so I can click off the master sheet to the personal sheet.

I have been able to get it to create a new tab, but not rename it, nor can I get the hyperlink to work as a relative reference (it always names the hyperlink after the person that I used when I created the macro).

I've attached my test spreadsheet if you need to look at it more closely - if you look at the "O'Connor" example, that's what I'd like it to look like. Any help would be much appreciated.

Thanks so much!!
Sarah

Hi Sarah. Try the attached file. I have added a column (AR) at the far right with a dropdown menu for you to choose "Done" when you are finished entering all the data for a particular person. When you choose "done" and then exit the cell, a new sheet will be added with the appropriate name and the balance will be copied over. Hopefully, this will work for you.