Reference Excel sheet number in an external workbook

Question

I have a workbook and in that workbook, I would like to make a reference to the external Workbook "Book4"

When I write:

Text = Workbooks("Book4").Worksheets("Text").Range("A2").Value

It works. Nevertheless, I'd prefer to make the reference using the CodeName rather than the sheet's real name, "Text" (Sheet1).

Yet when I enter the following code, which I would have thought would have worked, it doesn't appear to operate.

Text = Workbooks("Book4").Sheet1.Range("A2").Value

Can anybody please tell me what I'm doing wrong?

Answer 1

Try this:

Function SheetByCodeName(wb As Workbook, cn As String) As Worksheet
    Dim ws As Worksheet: For Each ws In wb.Worksheets
        If ws.CodeName = cn Then
            Set SheetByCodeName = ws
            Exit Function
        End If
    Next
    Set SheetByCodeName = Nothing
End Function

Use it like this:

Debug.Print SheetByCodeName( ThisWorkbook, "Sheet1" ).Name