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

 

[Fluff]

You can only use the sheet codename like that in the workbook containing the code.
Try using the sheet name instead.

 

[RoryA]

Why not just copy the one sheet you do want, so it creates a new workbook, then save it?

 

[KlaasE]

Do you mean I should change this:

If .CodeName <> "OrderformulierKopie" Then .Delete

to this?

If .Name <> "OrderformulierKopie" Then .Delete

This still gives me the same error

 

[Fluff]

You have an extra " which shouldn't be there, but is that the actual name of the sheet, as seen on the tab?
Or follow Rorya's advice.

 

[KlaasE]

I want all the macro's to copy with it. As far as I could find this was the only way?

 

[KlaasE]

The extra " was a mistake, but that is indeed the exact name of the sheet

 

[RoryA]

The error is because copynaam has the full file path and you can't use that as an index into the Workbooks collection. You could do this:

Private Sub cmdStart_Click()
   mkFolder (ThisWorkbook.Path & "\" & Ivlgnr)

   Dim CopyNaam As String
   CopyNaam = ThisWorkbook.Path & "\" & Ivlgnr & "\" & Ivlgnr & ".xlsm"

   ActiveWorkbook.SaveCopyAs CopyNaam

   Dim NewBook As Workbook
   Set NewBook = Application.Workbooks.Open(CopyNaam)

   Application.DisplayAlerts = False
   
   Dim theSheet As Object
   For Each theSheet In NewBook.Sheets
      If LCase$(theSheet.CodeName) <> "orderformulierkopie" Then theSheet.Delete
   Next
   Application.DisplayAlerts = True


   ThisWorkbook.Save

End Sub

 

[Fluff]

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

 

[KlaasE]

Thanks RoryA! it did work, although it deleted every sheet, including orderformulierkopie