XLA Routines: EE_ReplaceSheet

Nick's picture
EE_ReplaceSheet is a useful routine that replaces a sheet, and does not error if the sheet does not exist meaning you can use the routine to create a new sheet.
Function EE_ReplaceSheet(strSheet As String) As Worksheet
    Dim wksNew              As Worksheet
    Dim wbk                 As Workbook
    Dim blnDisplayAlerts    As Boolean
 
    Set wbk = ActiveWorkbook
 
    On Error Resume Next
        Set wksNew = wbk.Worksheets.Add(after:=wbk.Worksheets(strSheet))
    Err.Clear: On Error GoTo 0: On Error GoTo -1
 
    If wksNew Is Nothing Then
        Set wksNew = wbk.Worksheets.Add(after:=wbk.Worksheets(ThisWorkbook.Worksheets.Count))
        wksNew.Name = strSheet
        Set wksNew = Nothing
        Exit Function
    End If
 
    blnDisplayAlerts = Application.DisplayAlerts
    Application.DisplayAlerts = False
 
    On Error Resume Next
        wbk.Worksheets(strSheet).Delete
        wksNew.Name = strSheet
    Err.Clear: On Error GoTo 0: On Error GoTo -1
 
    Application.DisplayAlerts = blnDisplayAlerts
    Set EE_ReplaceSheet = wksNew
 
    Set wksNew = Nothing
    Set wbk = Nothing
End Function