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

Johnny Thunder

Well-known Member
Joined
Apr 9, 2010
Messages
693
Office Version
  1. 2016
Platform
  1. MacOS
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.

Code:
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
 

Excel Facts

Repeat Last Command
Pressing F4 adds dollar signs when editing a formula. When not editing, F4 repeats last command.
Hi John,

Try this:

Code:
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
 
Upvote 0
Thanks Robert, worked like a charm. So, if I understand this correctly, the Goto 0 line is returning the code to the start?
 
Upvote 0
No, it nullifies (turns off) the suppressing of error messages.
 
Upvote 0

Forum statistics

Threads
1,224,829
Messages
6,181,218
Members
453,024
Latest member
Wingit77

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top