Option Explicit

Private xlApp As ClassEventsxlApp

Private Sub Workbook_Open()
    Set xlApp = New ClassEventsxlApp
End Sub

Option Explicit
Private WithEvents xlApp As Application

Private Sub Class_Initialize()
    Set xlApp = Application
End Sub

Private Sub xlApp_NewWorkbook(ByVal Wb As Workbook)
    Dim Sh As Worksheet
    For Each Sh In Wb.Worksheets
        Wb.VBProject.VBComponents(Sh.Name).Name = Replace(Sh.Name, "Лист", "Sheet")
        Sh.Name = Replace(Sh.Name, "Лист", "Sheet")
    Next Sh
End Sub

Private Sub xlApp_WorkbookNewSheet(ByVal Wb As Workbook, ByVal Sh As Object)
    ActiveWorkbook.VBProject.VBComponents(Sh.Name).Name = Replace(Sh.Name, "Лист", "Sheet")
    Sh.Name = Replace(Sh.Name, "Лист", "Sheet")
End Sub

CallByName Worksheets(1), "_CodeName", VbLet, "Sheet1"

Sub Test()
   Dim sh As Worksheet
   For Each sh In ActiveWorkbook.Worksheets
     If LCase(sh.CodeName) Like "лист*" Then
       CallByName sh, "_Codename", VbLet, Replace(sh.CodeName, "Лист", "Sheet", compare:=vbTextCompare)
       DoEvents
     End If
   Next sh
End Sub