Fully reference a worksheet by codename

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.

You can do this by adding the second Workbook as a reference, and calling the workbook by the VBA Project name.

Obviously, it's a good idea to change the VBA project name from the default 'VBAProject'!

I tested this by creating two new workbooks each with one sheet, with a value of 1 or 2 in cell A1. The VBA projects were named 'Proj1' and 'Proj2', and the worksheets' CodeNames were left as 'Sheet1'.

This is the code I used:

Sub test()

    Debug.Print Proj1.Sheet1.Cells(1, 1)
    Debug.Print Proj2.Sheet1.Cells(1, 1)

End Sub

Yielding an output of:

1
2