Excel tab sheet names vs. Visual Basic sheet names

In the Excel object model a Worksheet has 2 different name properties:

Worksheet.Name
Worksheet.CodeName

the Name property is read/write and contains the name that appears on the sheet tab. It is user and VBA changeable

the CodeName property is read-only

You can reference a particular sheet as Worksheets("Fred").Range("A1") where Fred is the .Name propertyor as Sheet1.Range("A1") where Sheet1 is the codename of the worksheet.

This will change all worksheet objects' names (from the perspective of the VBA editor) to match that of their sheet names (from the perspective of Excel):

Sub ZZ_Reset_Sheet_CodeNames()'Changes the internal object name (codename) of each sheet to it's conventional name (based on it's sheet name)    Dim varItem As Variant    For Each varItem In ThisWorkbook.VBProject.VBComponents        'Type 100 is a worksheet        If varItem.Type = 100 And varItem.Name <> "ThisWorkbook" Then            varItem.Name = varItem.Properties("Name").Value        End If    NextEnd Sub

It is important to note that the object name (codename) "(Name)" is being overridden by the property name "Name", and so it must be referenced as a sub-property.

You should be able to reference sheets by the user-supplied name. Are you sure you're referencing the correct Workbook? If you have more than one workbook open at the time you refer to a sheet, that could definitely cause the problem.

If this is the problem, using ActiveWorkbook (the currently active workbook) or ThisWorkbook (the workbook that contains the macro) should solve it.

For example,

Set someSheet = ActiveWorkbook.Sheets("Custom Sheet")