Answers ( 5 )

  1. 2017-01-05 06:01

    Use double quote before and after Sheet1

    With Worksheets("Sheet1")   '* Error here 
      'my code here
    End With
    
  2. 2017-01-05 07:01

    You can use sheet codenames directly in your code as if they were declared variables:

    Sub UsingSheetCodeName()
        With Sheet1   
           .[a1] = Sheet1.Name
        End With
    End Sub
    
  3. 2017-01-05 07:01

    To use the Worksheet.Index in your code:

    With Worksheets(1) ' << this is the index for Sheet1
        .Range("A1").Value = "Test" ' modify the value in cell A1
    End With
    
  4. 2017-01-05 10:01

    Maybe this code helps understand the different names and the index

    Sub DisplaySheetnames()
    
        Dim wks As Worksheet
        For Each wks In Worksheets
            Debug.Print "Index", wks.Index, "of sheet with name: " & wks.Name, "and", "codename " & wks.CodeName
        Next
    
    End Sub
    
  5. 2017-01-05 10:01

    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 Integer
    
    ' 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
    
            Exit For
    
        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

◀ Go back