Fully reference a worksheet by codename

by

Referring to a sheet by its codename always implies the sheet in ThisWorkbook, i.e. the workbook that contains the code you are executing.

There seems to be no straightforward way to fully qualify a sheet in a different workbook using its codename.

This function will help you do this:

Function GetSheetWithCodename(ByVal worksheetCodename As String, Optional wb As Workbook) As Worksheet
    Dim iSheet As Long
    If wb Is Nothing Then Set wb = ThisWorkbook ' mimics the default behaviour
    For iSheet = 1 To wb.Worksheets.Count
        If wb.Worksheets(iSheet).CodeName = worksheetCodename Then
            Set GetSheetWithCodename = wb.Worksheets(iSheet)
            Exit Function
        End If
    Next iSheet
End Function

Example usage:

GetSheetWithCodename("Sheet1", Workbooks("Book2")).Cells(1, 1) = "Sheet1 in Book2"
GetSheetWithCodename("Sheet1", ActiveWorkbook).Cells(1, 1) = "Sheet1 in ActiveWorkbook"
GetSheetWithCodename("Sheet1").Cells(1, 1) = "Sheet1 in ThisWorkbook"

Note that the last line is equivalent to simply saying:

Sheet1.Cells(1, 1) = "Sheet1 in ThisWorkbook"

because, as mentioned above, referring to a sheet by its codename always imply the sheet in ThisWorkbook.

More Related Contents:

  1. How do you change the value of a cell in excel by clicking on it using VBA? [closed]
  2. How do I use FileSystemObject in VBA?
  3. Run Excel Macro from Outside Excel Using VBScript From Command Line
  4. Importing text file into excel sheet
  5. Filling any empty cells with the value above
  6. Password hash function for Excel VBA
  7. VBA Excel sort range by specific column
  8. Select method of Range class failed via VBA
  9. Delete cells in an Excel column when rows = 0
  10. VBA Many buttons point to the same _Click sub
  11. Excel VBA Filter Change event handler
  12. How to suppress Update Links warning?
  13. How do we use restful APIs from Excel macros (vba)?
  14. Cannot VBA write data to cells in Excel 2007/2010 within a function
  15. How to send email to multiple recipients with addresses stored in Excel?
  16. What are the benefits of using Classes in VBA? [closed]
  17. Writing a VLOOKUP function in vba
  18. Handle cancellation of InputBox to select range
  19. Type Mismatch in Loop to scan Outlook Messages [duplicate]
  20. VBA: How to combine two ranges on different sheets into one, to loop through
  21. Delay macro to allow events to finish
  22. Export multiple sheets to PDF simultaneously without using ActiveSheet or Select
  23. declaring a unicode string in vba in excel [duplicate]
  24. How to programmatically code an ‘undo’ function in Excel-Vba? [duplicate]
  25. Change the Point Color in chart excel VBA
  26. Extract URL From Excel Hyperlink Formula
  27. What is the different between IsNull, IsEmpty, =Empty, and an empty string ie “” and why might I use variants
  28. AutoFilter – Use of SpecialCells
  29. How to detect if user select cancel InputBox VBA Excel
  30. Change HTML email body font type and size in VBA

Leave a Comment