Error in macro for deleting unwanted worksheets

[Nithya]

I am using the below macro for deleteing all the worksheets except sheet 21 and sheet 22 from a dump of xls files in c:\temp path, the code was working fine., but now suddenly it is prompting to select each file before deleting the worksheets and after delteting it is prompting the user to save the files.

Below is the code., can any one pls have a look.

   [FONT=Calibri][COLOR=#000080]Sub  Delete_Trans()
    
    Dim strFldrPath As String
    
     strFldrPath = "C:\temp"[/COLOR][/FONT]
 [FONT=Calibri][COLOR=#000080]    Dim  CurrentFile As String
    Dim wb As Workbook, ws As Worksheet
     Application.ScreenUpdating = False[/COLOR][/FONT]
  
 [FONT=Calibri][COLOR=#000080]     CurrentFile = Dir(strFldrPath & "\" & "*.xls")[/COLOR][/FONT]
  
 [FONT=Calibri][COLOR=#000080]    While  CurrentFile <> vbNullString
        Set wb = Workbooks.Open(strFldrPath  & "\" & CurrentFile)
        If SheetExists("sheet 21", wb) And  SheetExists("sheet 22", wb) Then
              Application.DisplayAlerts =  False
          For Each ws In wb.Sheets
              If Not (ws.Name =  "sheet 21" Or ws.Name = "sheet 22") Then
                 ws.Delete
              End If
          Next ws
            
          End If
        wb.Close True
        CurrentFile =  Dir
    Wend[/COLOR][/FONT]
  
 [FONT=Calibri][COLOR=#000080]     Application.ScreenUpdating = True
    
End Sub[/COLOR][/FONT]
  
 
[FONT=Calibri][COLOR=#000080]Private Function SheetExists(SheetName  As String, wb As Workbook) As Boolean
    
    Dim wsCheck As  Worksheet
    On Error GoTo NotFound
    Set wsCheck =  wb.Sheets(SheetName)
    SheetExists = True
    Exit Function
     
NotFound:
    SheetExists = False
    
End Function  
[/COLOR][/FONT]

I don't want the macro to prompt., it has to delete the unwanted sheets from c:\temp location and save them automatically with out any manual intervention.

Regards,
Nithya

 

[Nithya]

Can anyone pls tell me what is wrong in the code

 

[Cindy Ellis]

Do all of the workbooks have both sheet21 and sheet22? If not, then the Application.displayalerts never gets set to false.
(It would probably be a good idea to turn displayalerts back on at the end of your code)
...Don't know if that's the problem, but it's the only thing that jumps out at me.
Cindy

 

[taurean]

I think Cindy is right. I just tested it with a small sample and it worked without giving me any alerts for manual intervention. Here's the code:

Sub Delete_Trans()
Const strFldrPath As String = "C:\temp"
Dim CurrentFile As String
Dim wb As Workbook
Dim ws As Worksheet
Application.ScreenUpdating = False
Application.DisplayAlerts = False
CurrentFile = Dir(strFldrPath & "\" & "*.xls")
  
While CurrentFile <> vbNullString
Set wb = Workbooks.Open(strFldrPath & "\" & CurrentFile)
   If SheetExists("sheet 21", wb) And SheetExists("sheet 22", wb) Then
        For Each ws In wb.Sheets
            If Not (ws.Name = "sheet 21" Or ws.Name = "sheet 22") Then
            ws.Delete
            End If
        Next ws
    End If
    wb.Close True
CurrentFile = Dir
Wend
  
Application.DisplayAlerts = True
Application.ScreenUpdating = True
    
End Sub
Private Function SheetExists(SheetName As String, wb As Workbook) As Boolean
    
    Dim wsCheck As Worksheet
    On Error GoTo NotFound
    Set wsCheck = wb.Sheets(SheetName)
    SheetExists = True
    Exit Function
     
NotFound:
    SheetExists = False
    
End Function