XLA routines: EE_DeleteTempSheets

Nick's picture
EE_DeleteTempSheets is used when your routine creates temp sheets that need to be deleted at the end of the routine. Pass in the essential sheets, and the others will be deleted.
Sub EE_DeleteTempSheets(ArrayOrRange)
'> - takes an array or range
'> - deletes any sheets that are not contained in the array\range
    Dim blnDisplayAlerts    As Boolean
    Dim arr
    Dim intWksCount         As Integer
    Dim blnDelete           As Boolean
    Dim wbk                 As Workbook
    Set wbk = ActiveWorkbook
    Select Case TypeName(ArrayOrRange)
        Case "Variant()"
            arr = ArrayOrRange
        Case "Range"
            arr = Application.Transpose(ArrayOrRange)
        Case "String"
            arr = Array(ArrayOrRange)
        Case Else
            Exit Sub
    End Select
    Dim intShtAdded As Integer
    For intWksCount = 1 To wbk.Worksheets.Count
        On Error Resume Next
        Call Application.WorksheetFunction.Match(wbk.Worksheets(intWksCount).Name, arr, 0)
        If Err.Number <> 0 Then
            If intShtAdded = 0 Then
                ReDim arrTemp(0)
                ReDim Preserve arrTemp(UBound(arrTemp) + 1)
            End If
            intShtAdded = intShtAdded + 1
            arrTemp(UBound(arrTemp)) = wbk.Worksheets(intWksCount).Name
            blnDelete = True
        End If
        Err.Clear: On Error GoTo 0: On Error GoTo -1
    Next intWksCount
    If blnDelete = True Then
        blnDisplayAlerts = Application.DisplayAlerts
        Application.DisplayAlerts = False
        Application.DisplayAlerts = blnDisplayAlerts
    End If
    Erase arr
    Erase arrTemp
    Set wbk = Nothing
End Sub