Macro has errors if Visual Basic Editor is not open

I've been helping another user create a workbook that dynamically adds, renames and deletes worksheets from a "Main Sheet".

I have got the whole thing figured out and running to satisfaction, except...

The macro runs fine if the Visual Basic Editor is open. If the editor is closed while the macro is run, I get "Runtime Error '9': Subscript out of range"

Any ideas what could be casing this? The errors occur when attempting to add sheets.

Here's my "UpdateAll" macro that is having the issues:

Sub UpdateAll()
    
    Dim SheetsAddRename As Range
    Dim shts As Worksheet
    Dim SheetTest As String
    Dim RowsDelete As Range
    
    For Each SheetsAddRename In Sheets("Main Sheet").Range("C2:C22")
        SheetTest = False
        If SheetsAddRename = "" Then
            Sheets("Main Sheet").Activate
            Call DeleteRows
            Call Resync
            Exit Sub

        Else
            
            For Each shts In ThisWorkbook.Worksheets
                If shts.CodeName = SheetsAddRename.Offset(0, -1) And Sheets("Main Sheet").Range(SheetsAddRename.Address).Offset(0, 1).Value = "" Then
                    shts.Name = SheetsAddRename
                    SheetTest = True
                ElseIf shts.CodeName = SheetsAddRename.Offset(0, -1) And Sheets("Main Sheet").Range(SheetsAddRename.Address).Offset(0, 1).Value <> "" Then
                    Sheets(SheetsAddRename.Value).Delete
                    SheetTest = True
                End If
            Next shts
            
            If SheetTest = False And Sheets("Main Sheet").Range(SheetsAddRename.Address).Offset(0, 1).Value = "" Then
                Worksheets.Add(After:=Worksheets(Worksheets.Count)).Name = SheetsAddRename.Value
                SheetsAddRename.Offset(0, -1) = ActiveSheet.CodeName
            End If
            
        End If
        
    Next SheetsAddRename

End Sub

It seems to break-down here:

If SheetTest = False And Sheets("Main Sheet").Range(SheetsAddRename.Address).Offset(0, 1).Value = "" Then
                Worksheets.Add(After:=Worksheets(Worksheets.Count)).Name = SheetsAddRename.Value
                SheetsAddRename.Offset(0, -1) = ActiveSheet.CodeName
            End If

When it goes to update the Main Sheet with the new worksheet's codename, ActiveSheet.CodeName seems to be blank.

Again, this only happens if the Visual Basic Editor is not open. It is as if excel cannot get the codename of a sheet if the editor is not open.

Any help would be much appreciated.