Refer to sheet using codename

Question

I get a "type mismatch" error in this code:

With Worksheets(Sheet1)   '* Error here 
   'my code here
End With

My sheet's CodeName is 'sheet1'.

Can someone please help me remove the error?

Answer 1

1) Refer to sheet by Index:

With Worksheets(1) 
    '<stuff here>
End With

The `Index' is dependent on the "order of sheets in the workbook". If you shuffle your sheets order, this may not refer to the same sheet any more!

2) Refer to sheet by Name:

With Worksheets("Your Sheet Name") 
    '<stuff here>
End With

This is the .Name property of a worksheet, and is the name visible in the Excel worksheet tab and in brackets in the VBA Project Explorer.

3) Refer to sheet by CodeName:

You suggested you actually wanted to use the .CodeName property of a worksheet. This cannot be reference within brackets like the above two examples, but does exist contrary to some answers above! It is assigned automatically to a sheet on creation, and is "Sheet" then the next unused number in the previously created CodeNames.

The advantage of using CodeName is that it doesn't depend on the sheet order (unlike the Index) and it doesn't change if a user changes the Name simply by renaming the sheet in Excel.

The disadvantage is the code can be more convoluted or ambiguous. Since CodeName is read-only [1] this cannot be improved, but does ensure the above advantages! See the referenced documentation for more details.

First way of using it: directly...

With Sheet1
    '<stuff here>
End With

Second way of using it: indirectly, may offer more clarity or flexibility, shows how to use the CodeName property of a worksheet...

By looping over sheets and reading the CodeName property, you can first find either the Index or Name property of your desired sheet. Then your can use this to reference the sheet.

Dim sh as WorkSheet
Dim shName as String
Dim shIndex as Long

' Cycle through all sheets until sheet with desired CodeName is found
For Each sh in ThisWorkbook.WorkSheets        
    ' Say the codename you're interested in is Sheet1
    If sh.CodeName = "Sheet1" Then            
        ' - If you didn't want to refer to this sheet later, 
        '   you could do all necessary operations here, and never use shName
        '   or the later With block.
        ' - If you do want to refer to this sheet later,
        '   you will need to store either the Name or Index (below shows both)

        ' Store sheet's Name
        shName = sh.Name            
        ' Store sheet's Index
        shIndex = sh.Index           
    End If
Next sh 

' Check if match was found, do stuff as before if it was!
If shName = "" Then
    MsgBox "Could not find matching codename"
Else 
    ' Equally to the next line, could use Worksheets(shIndex)
    With Worksheets(shName)
        '<stuff here>
    End With
End If

[1] https://msdn.microsoft.com/en-us/library/office/ff837552.aspx