Naming Worksheets with a Variable without using a cell

[Shiseiji]

I've been working with naming worksheets, and all the code I've found uses a cell then names the worksheet the cell contents. It got me wondering and searching to see if it's possible to bypass using a cell. I don't know if it's impossible or I'm not searching with the correct terms. Is there a way to bypass using a cell?

This is what I'm using now. The last column +1 is simply a known empty cell.

Cells(1, LastCol).Offset(0, 1).Name = "cel_CDate1"
Range("cel_CDate1").Offset(0, 1).Name = "cel_CDate2"
    With Range("cel_CDate2")
        .NumberFormat = "mm-dd-yyyy"
        .Formula = "=""User Accounts "" & TEXT(TODAY(),""mm-dd-yy"")"
        .Copy
        .PasteSpecial xlPasteValues
    End With
    '
    Sheet1.Name = Range("cel_CDate2").Value
    ws.Range("cel_CDate2").Clear
    wb.Names("cel_CDate2").Delete

In other words, is it possible to write code where the Sheet1.Name = Range("cel_CDate2").Value is replaced with "something" like
Sheet1.Name = ?????
.Formula = "=""User Accounts "" & TEXT(TODAY(),""mm-dd-yy"")"
and bypassing the need to use a cell?

Thanks for helping me to learn.

Ron

 

[Fluff]

Like

Sheet1.Name = "User Accounts " & Format(Date, "mm-dd-yy")

 

[Shiseiji]

Oh my . . . One day I hope I'll get it figured out why Format(Date, "mm-dd-yy") is the same as TEXT(TODAY(),""mm-dd-yy"")

Appreciate the help!

Thanks

Ron

 

[Fluff]

Glad to help & thanks for the feedback.