Deleting Worksheets except for one, in Copy of workbook

[KlaasE]

Hello,

I'm am trying to make a copy of a workbook, which I then open and then I want to delete all sheets except for the sheet "OrderformulierKopie". The code I'm using right now keeps giving the error it is out of range (in the for loop). I can't figure out how to fix it.
Ivlgnr is made in another sub, defined like this: Ivlgnr = "DIS" & Format(Now(), "yy") & "-" & Format(volgnummer, "0000")

Any advice is welcome!

Private Sub cmdStart_Click()    
    mkFolder (ThisWorkbook.Path & "\" & Ivlgnr)
    Dim CopyNaam As String
    CopyNaam = ThisWorkbook.Path & "\" & Ivlgnr & "\" & Ivlgnr & ".xlsm"
    
    ActiveWorkbook.SaveCopyAs CopyNaam
    Application.Workbooks.Open CopyNaam
    
    Dim iterator As Long
    Application.DisplayAlerts = False
    For iterator = Workbooks(CopyNaam).Worksheets.Count To 1 Step -1
        With Workbooks(CopyNaam).Worksheets(iterator)
            If .CodeName <> "OrderformulierKopie" Then .Delete
        End With
    Next iterator
    Application.DisplayAlerts = True
    
    
    ThisWorkbook.Save
    End
End Sub

 

[RoryA]

That shouldn't be possible - a workbook must contain at least one sheet.

It suggests that that is not in fact the code name of the sheet.

 

[RoryA]

And there was me thinking you could only directly refer to the codename in ThisWorkbook.

You can read the codename property, you just can't use the codename as a variable. (Well, you can, but only if you set a reference between the projects)

 

[Fluff]

Gotcha, thanks for that.

 

[KlaasE]

Ah I see I mixed up sheet name and code name. I got it working now. Thanks!