VBA Help - If worksheet(s) Exist then......

[Johnny Thunder]

Hi guys, working on a code that will look into a newly opened workbook for either sheets ("R&O", "Executive Summary") if either or the sheets exists, delete them.

I came up with the below but do not know how to modify for multiple sheets and in the event one of the tabs don't exists look for the other.

Any help is appreciated!

Sub find ()

Dim ws             as worksheet
Dim Destbook    as workbook

Set DestBook = Workbooks.Open(vFilename) 'vfilename is defined on my full code

 For Each ws In DestBook.Worksheets 'Searches to ensure Sheets aren't already in workbook
         If ws.Name = "R&O" Then 
             Sheets("R&O").Delete
             End
    End If
        Next

End Sub

 

[alansidman]

         If ws.Name = "R&O" Then 
             Sheets("R&O").Delete

Change the above to

If ws.Name = "R&O" or ws.Name = [COLOR=#333333]"Executive Summary" then
ws.delete[/COLOR]

 

[StephenCrump]

As an alternative to Alan's solution, you could also do it without looping through all worksheets:

Application.DisplayAlerts = False
On Error Resume Next
With Destbook
    .Worksheets("R&O").Delete
    .Worksheets("Executive Summary").Delete
End With
On Error GoTo 0
Application.DisplayAlerts = True

Just FYI, if you google "worksheet exists site:mrexcel.com" you'll find many succinct variations on a function that will test whether a worksheet exists. These rely on referencing the worksheet and catching the error if it doesn't exist.