VBA coding: macro to create a new sheet and have it named by data in a cell and the date

The code I have right now will create a new sheet and insert the date, as the name of the sheet, but I'd like the code to do that and use data from a cell, as the name of the worksheet.

here's the current code:

Sub CheckAndAddSheetNameAsCurrentDate()

Dim SheetName As String

SheetName = Format(Date, "mm-dd-yy")

On Error GoTo AddNew
Sheets(SheetName).Activate
Exit Sub

AddNew:
Sheets.Add , Worksheets(Worksheets.Count)
ActiveSheet.Name = SheetName

End Sub


For bonus points, I'm trying to find out how to add a static date using a macro, that when a new worksheet template is opened it inserts the current date into this cell, and then NEVER changes it. I'm also looking to share a workbook, but lock a worksheet of that workbook. And I need to then create a worksheet of this workbook where all of this data is updated, but on a day by day basis and its clearly stated what day the data is from. (I have more requests, but I'm working this through.)

It didn't work, it said subscript is out of range...besides, what I'm trying to do is have a button that opens a new worksheet and names that worksheet with the current date and my name, which is specific data in a cell in the worksheet.

I got some help in the VBA side of the forum, it works. Thanks though...now I just need to figure out the other bits...