Refer to sheet using codename

by

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

More Related Contents:

  1. Excel tab sheet names vs. Visual Basic sheet names
  2. Query my excel worksheet with VBA
  3. using for loop for vba
  4. delete Autofilter Field resullt using the header Name
  5. Somehow it’s not copying [closed]
  6. VBA Macro On Timer style to run code every set number of seconds, i.e. 120 seconds
  7. Assign code to a button created dynamically
  8. Last not empty cell (column) in the given row; Excel VBA
  9. HTML Text with tags to formatted text in an Excel cell
  10. Split comma separated entries to new rows [closed]
  11. Get list of Excel files in a folder using VBA [duplicate]
  12. How do I base64 encode a string efficiently using Excel VBA?
  13. Select first empty cell in column F starting from row 1. (without using offset )
  14. Excel VBA – QueryTable AfterRefresh function not being called after Refresh completes
  15. Excel VBA: AutoFill Multiple Cells with Formulas
  16. VBA.Find and adding a value
  17. excel vba- extract text between 2 characters
  18. Round function in Excel, worksheet function vs VBA
  19. How to continue the code on the next line in VBA
  20. Excel VBA Userform – Execute Sub when something changes
  21. VBA: How to change the value of another cell via a function?
  22. Count the number of rows in another sheet [duplicate]
  23. Getting Excel to refresh data on sheet from within VBA
  24. Range.Find not making a difference between January and November (February and December) in VBA Excel
  25. VBA Password Input with Cancel Function
  26. Finding first blank row, then writing to it
  27. Reducing WithEvent declarations and subs with VBA and ActiveX
  28. How to have vba execute every 10 minutes?
  29. Read and write from/to registry in VBA
  30. Unable to read data from a CSV using ADO due to the driver thinking I am working with integers/number and showing nulls instead of text

Leave a Comment