How to Change a Sheet's Code Name

shawleigh17

Board Regular
Joined
Nov 16, 2007
Messages
79
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?
 

Some videos you may like

Excel Facts

Do you hate GETPIVOTDATA?
Prevent GETPIVOTDATA. Select inside a PivotTable. In the Analyze tab of the ribbon, open the dropown next to Options and turn it off

lenze

Legend
Joined
Feb 18, 2002
Messages
13,690
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

Board Regular
Joined
Nov 16, 2007
Messages
79
I add the sheets like this (just one example):

Code:
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).
 

Watch MrExcel Video

Forum statistics

Threads
1,127,753
Messages
5,626,665
Members
416,199
Latest member
Gautamsunil

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top