Preventing An Error

[Ark68]

I have this code (only a portion presented here...):

Sub clean_up_database()

    Dim norec As Long 'number of records
    Dim red_ref As Long
    Dim red_fun As Long
    Dim red_fac As Long
    Dim red_class As Long
    Dim class_c As Long
    Dim myVal As Variant
    Dim dv As Long
    
    Set wb_main = Workbooks("Sports15b.xlsm")
    Set ws_vh = wb_main.Worksheets("VAR_HOLD")
    Set ws_th = wb_main.Worksheets("TEMP_HOLD")
    Set ws_rd = wb_main.Worksheets("Rental_Data")
    Set ws_fac = wb_main.Worksheets("Facilities")
    Set ws_front = wb_main.Worksheets("DYNAMIC")
    Set fac1 = ws_fac.Range("A:H")
    Set class1 = ws_fac.Range("B:B")
    
    qfile = ws_vh.Range("B3")
    
    Workbooks.Open ("H:\PWS\Parks\Parks Operations\Sports\Sports15\DATA1\" & qfile)
        
    Set wb_data = Workbooks(qfile)
    
    With wb_data
        .Worksheets.Add(After:=.Worksheets(.Worksheets.Count)).Name = "STAFF"
        .Sheets("Sheet1").Name = "CORE"
        Set ws_core = wb_data.Worksheets("CORE")
        'Set ws_core = wb_data.Worksheets("CORE")
        Set ws_staff = wb_data.Worksheets("STAFF")
    End With
    ...

I know why I am getting an error ('Cannot rename a sheet to the same name as another sheet, a referenced object library or a workbook referenced by visual basic') with the line in red. It's because this procedure has already been run already and has already added these sheets previously. The user may have gotten to this procedure by accidentally pressing a wrong command button.

I am wondering what the best way is to catch this error before it happens? If the user gets to this point and tries to open this file having already completed these tasks, identify they shouldn't be here, and exit to the home page. (worksheet("Dynamic"))

 

[mikerickson]

This will alert you if there is already a sheet named "STAFF"
If the flag is True (i.e. if it already exists), you can either exit the sub, or rename the existing sheet or use a different name for the new sheet, depending on your pleasure.

Dim flag as boolean

Flag = False
On Error Resume Next
Flag = (LCase(ThisWorkbook.Sheets("Staff").Name) = "staff")
On Error goto 0

If Flag then 
    MsgBox "There is already a sheet named Staff"
End If

 

[Ark68]

Works like a charm! Thank you Mike.