How to Change a Sheet's Code Name

[shawleigh17]

I need to change the code name of a sheet upon creation. Right now, I delete and create sheets with the same name, but the code names all start with "Sheet" and a number. Right now, every time I create the sheet, the number increases. I just want it to change that name, so that I don't end up with Sheet333, or something. I'm pretty sure that those run out eventually, and this will need to be run several times. I know that I can just delete the contents of the sheet, but right now everything is written to just create sheets, and I do this several times, so if there is a way to change the code name, I would rather do that first, than risk deleting stuff where I shouldn't.

I tried

ThisWorkbook.VBProject.VBComponents("Sheet2").Name= "NewCodeName"

with Brand in both places for Sheet2 and NewCodeName. I access the files with Sheets("Brand"), so that I don't have to mess with the code name, but I want to change the code name to match the sheet name. Anybody have suggestions?

 

[lenze]

So, how are you adding the sheets? By code or manually. What do you want the name(s) to be? User Defined? A formula result? A cell Value?
lenze

 

[shawleigh17]

I add the sheets like this (just one example):

Sheets("Brand").Visible = True
    Application.DisplayAlerts = False
    On Error Resume Next
    Sheets("Brand").Delete
    Application.DisplayAlerts = True
    On Error GoTo 0
    Sheets.Add().Name = "Brand"
    Sheets("Brand").Select

I want the Sheet's Code Name to be the same as the name given in the above code, so for this example I want it to be "Brand." So when I look to the left, where all of my sheets are located, I want it to look like Brand(Brand).