Refer to worksheet's codename

[tiredofit]

I have a master workbook which opens data workbooks.

Each data workbook only contains two worksheets.

The names of the worksheets in each data workbook are different but their codenames are static, say wksyear1 and wksyear2.

How can I refer to the data workbooks' worksheets by its codename?

This code doesn't work because it doesn't know what wksyear1 is.

Dim ws As Worksheet

Set ws = Workbooks(FileName.Name).Worksheets(wksyear1))

Thanks

 

[Jaafar Tribak]

Duplicate deleted.

 

[davesexcel]

It is easy to use the sheet code name for the active workbook.
For using a different workbook, you may have to loop through the sheets first to find the codename, then set the sheet variable

Sub getSheetName()
    Dim wb As Workbook, s As String, ws As Worksheet, sh As Worksheet
    Set wb = Workbooks("TrialSheet.xlsx")
    s = "Sheet1"  'sheet codename to find
    With wb
        For Each sh In .Sheets
            If sh.CodeName = s Then
                Set ws = .Sheets(sh.Name)
                Exit For
            End If
        Next
    End With
    MsgBox ws.Name
    ws.Range("D1") = "Hello"
End Sub

 

[Jaafar Tribak]

You can use an iterating function like this :

Function GetWorksheet(ByVal wb As Workbook, ByVal sSheetCodeName As String) As Worksheet
    Dim ws As Worksheet
    For Each ws In wb.Sheets
        If ws.CodeName = sSheetCodeName Then
            Set GetWorksheet = ws: Exit Function
        End If
    Next ws
End Function

Then call it like this:

Dim ws As Worksheet
Set ws = GetWorksheet(Workbooks(Filename.Name), "wksyear1")

 

[tiredofit]

Thanks to both for your swift response.