Changing sheet codename Run-Time Error 9: Subscript out of range

@Comintern already got you a working solution, but this code doesn't pollute your Immediate Window, and uses the hidden _CodeName property to change the sheet name instead of accessing the vbComponents collection.

It also uses an early-bound Worksheet assignment to wks, and then a With block because it is accessing more than 1 member of wks.

Interestingly, the placement of the VBProject member usage is important.

Dim wbk As WorkbookDim wks As WorksheetSet wbk = ThisWorkbookSet wks = wbk.Worksheets.Add'Oddly, this statement MUST appear AFTER the Worksheets.addDebug.Assert wbk.VBProject.Name <> vbNullString 'Don't pollute the Immediate windowWith wks  .Name = "Admin - Save Log"  .[_CodeName] = "wksAdminSaveLog"End With

If you need the VBE to have been opened, you can "trick" the debugging context into doing it for you. This seems to do whatever the project needs to update its indexing by forcing the VBE.MainWindow into existence:

Dim wbk As WorkbookDim wks As WorksheetSet wbk = ThisWorkbookSet wks = wbk.Sheets.Addwks.Name = "Admin - Save Log"Debug.Print wbk.VBProject.VBE.MainWindow.Caption   '<--Force the VBE to exist.wbk.VBProject.VBComponents(wks.CodeName).Name = "wksAdminSaveLog"

Edit:

It seems that simply obtaining the reference to the VBE.MainWindow is enough (see the comments). This also works:

Dim editor As ObjectSet editor = wbk.VBProject.VBE.MainWindow   '<--Force the VBE to exist.

Another simple method to force refreshing of the VBComponents Collection:

PropertyGetDiscard ThisWorkbook.VBProject.VBComponents.Count

Private Sub PropertyGetDiscard(AnyPropertyGet): End Sub

The procedure PropertyGetDiscard is used to avoid polluting the Immediate window or using a superfluous variable