Excel vba delete sheets not user named

Question

I have several workbooks in a directory. I want to read each workbook, and delete each sheet named, sheet1, sheet2, sheet3, etc.

I do not want to delete a user named worksheet, i.e. Month, Users, Cars, etc.

Example:

Workbook1

Month, Sheet1, Sheet2, Sheet3, ....Sheet50

Workbook2

Users, Sheet1, Sheet2, Sheet3, ....Sheet50

I want to delete Sheet1 - Nth number of Sheets.

Here is the code I am using to read the directory:

Sub ExecuteA12CT()

' Declare the variables

   Dim MyPath As String
   Dim MyFile As String
   Dim wbOpen As Workbook

' Define the path to the folder containing the target files (change accordingly)

   MyPath = "C:\My Workbooks\"
   

' Call the first .xls file (change the file extension accordingly)

   MyFile = Dir(MyPath & "*.xls")
   

' Loop through each file in the folder

   Do While Len(MyFile) > 0
   

' Open the current file

       Set wbOpen = Workbooks.Open(Filename:=MyPath & MyFile)
       

' Call your macro here

       Call A12CT
               

' Close the current file (change the setting for this argument accordingly)

     '  wbOpen.Close savechanges:=True
      ActiveWorkbook.Close True

' Call the next file

       MyFile = Dir
       
   Loop
   End Sub

I used a regular macro code with the above loop

   Sheets("Sheet1").Select
   ActiveWindow.SelectedSheets.Delete
   Sheets("Sheet2").Select
   ActiveWindow.SelectedSheets.Delete

but it gives an error code - Subcript out of range"

The first sheet is always named with a user given name,

i.e, Months, Years, Cars, Devices, etc. in every workbook.

I assumed that the code would skip the first worksheet and delete the remainder sheets, i.e., sheet 1....nth sheet.

Thank you,

Answer

I assume no user named sheet will include the substring "Sheet1". You may need to make the code more discerning.

 with wbOpen.sheets(i)
   if .name = .codename then
      ' delete the sheet
   end if
 end if

as a possible example of a stricter test (although not foolproof)

Demo'd from the immediate window: ? activesheet.name Sheet1 ? activesheet.codename Sheet1 ? activesheet.name = activesheet.codename True

but here it goes

Sub ExecuteA12CT()

' Declare the variables

  Dim MyPath As String
  Dim MyFile As String
  Dim wbOpen As Workbook

' Define the path to the folder containing the target files (change accordingly)

  MyPath = "C:\My Workbooks\"
  

' Call the first .xls file (change the file extension accordingly)

  MyFile = Dir(MyPath & "*.xls")
  

' Loop through each file in the folder

  Do While Len(MyFile) > 0
  

' Open the current file

      Set wbOpen = Workbooks.Open(Filename:=MyPath & MyFile)
      
    for i = wbOpen.sheets.count to 1 step -1
      if instr(1,wbOpen.sheets(i).Name,"sheet",vbTextcompare) then
        Application.displayalerts = False
            wbOpen.sheets(i).Delete
        Application.displayalerts = True
      end if
    next
              

' Close the current file (change the setting for this argument accordingly)

    '  wbOpen.Close savechanges:=True
     ActiveWorkbook.Close True

' Call the next file

      MyFile = Dir
      
  Loop
  End Sub