VB to determine if Worksheet exists

[Rocky0201]

Hi,

I have two subs wigh are:

Sub 1 - cycling through a list in my Location Master sheet, I add a sheet with the sheet name defined in my Location Master sheet.

This works fine adding worksheets if the worksheet does not exist. The issue I have is determining if the worksheet already exisits. If no, add worksheet. If yes, update worksheet.

Sub 2 - cycling through my list in my Location Master sheet, I delete a worksheet with the sheet name defined in my Location Master sheet.

This works fine deleteing worksheets if the worksheet exisits. The issue I have is determining before the delete is the sheet actually exists.

I guess what I' asking is for a routine that I can call to set a switch if he Worksheet exists. I would pass this routine the same name I need to check.

This one routine would satisfy my need in both of the subs above.

Thanks.

 

[VoG]

Try like this

Function WorksheetExists(WSName As String) As Boolean
On Error Resume Next
WorksheetExists = Worksheets(WSName).Name = WSName
On Error GoTo 0
End Function

Sub test()
If Not WorksheetExists("Sheet1") Then
    MsgBox "Unable to proceed", vbExclamation, "Error"
    Exit Sub
Else
    Sheets("Sheet1").Delete
End If
End Sub

 

[Rocky0201]

Thanks so much VoG... looks like this will work...