VBA - Add "On Error Resume Next" to my working code"

[Johnny Thunder]

Hi guys, for the life of me I can't figure out how to add an "On Error Resume Next" line into my working code.

I have a macro that opens files in a directory and if it finds a specific tab name it deletes it, in the event the tab does not exist I want it to close the file and open the next file.

Here is my current code, any help is appreciated. Seems like my code would need to be modified with a IF statement to do this, just not sure how to go about updating.

Option Explicit


Public Sub DeleteSheet()


Dim sourceSheet As Worksheet, ws As Worksheet
Dim folder As String, filename As String
Dim destinationWorkbook As Workbook
    
    Application.DisplayAlerts = False
    Application.ScreenUpdating = False
    
'Worksheet in active workbook to be copied as a new sheet to the 160 workbooks
Set sourceSheet = ThisWorkbook.Worksheets("Executive Summary")
    
'Folder containing workbooks
folder = "\\Wb\acctg\shared\RPTBUD\CASH\TBT\Monthly Forecast\2018\Mar\File Test\Jonathan TBT Test\Terr Files\"
       
    filename = Dir(folder & "*.xls*", False)


Application.AskToUpdateLinks = False 'Supresses External links warning
    
    While Len(filename) <> 0
    
       Debug.Print folder & filename
             Set destinationWorkbook = Workbooks.Open(folder & filename)
    
    destinationWorkbook.Activate
        destinationWorkbook.Sheets("Executive Summary").Delete
                   
    destinationWorkbook.Close True
    filename = Dir()  ' Get next matching file
    
    Wend


End Sub

 

[Trebor76]

Hi John,

Try this:

Option Explicit
Public Sub DeleteSheet()

    Dim sourceSheet As Worksheet, ws As Worksheet
    Dim folder As String, filename As String
    Dim destinationWorkbook As Workbook
        
    With Application
        .DisplayAlerts = False
        .ScreenUpdating = False
    End With
        
    'Worksheet in active workbook to be copied as a new sheet to the 160 workbooks
    Set sourceSheet = ThisWorkbook.Worksheets("Executive Summary")
        
    'Folder containing workbooks
    folder = "\\Wb\acctg\shared\RPTBUD\CASH\TBT\Monthly Forecast\2018\Mar\File Test\Jonathan TBT Test\Terr Files\"
           
    filename = Dir(folder & "*.xls*", False)
    
    
    Application.AskToUpdateLinks = False 'Supresses External links warning
        
    While Len(filename) <> 0
        
        Debug.Print folder & filename
        Set destinationWorkbook = Workbooks.Open(folder & filename)
            
        destinationWorkbook.Activate
        On Error Resume Next
            destinationWorkbook.Sheets("Executive Summary").Delete
        On Error GoTo 0
                           
        destinationWorkbook.Close True
        filename = Dir()  ' Get next matching file
        
    Wend
    
    With Application
        .DisplayAlerts = True
        .ScreenUpdating = True
    End With

End Sub

Regards,

Robert

 

[Johnny Thunder]

Thanks Robert, worked like a charm. So, if I understand this correctly, the Goto 0 line is returning the code to the start?

 

[Trebor76]

No, it nullifies (turns off) the suppressing of error messages.

 

[Johnny Thunder]

I see, thanks for the help.

 

[Trebor76]

NP. I'm glad we got it sorted