Search and delete sheet, if it already exists

[2_nisia]

Hello,

I have a excel that has few boxes with drop down options. The first option is an empty cell (Choice = 1)
I would like my code to check if there is already something chosen inside that cell and find the name that the previous user had given it (in this example the user had named it office (picture below)), and if the new user tries to make a new choice, I want the code to go and delete the already existing sheet named "Taks_office"

The name of this sheet ("Taks_office") is adding what the user chose as a name, to the "Taks_". So later down the code I have the following command:


ActiveWorkbook.Sheets(Taks(Choice)).Visible = True 'Makes hidden sheet visible
Sheets(Taks(Choice)).Copy , Sheets(Sheets.Count) 'Copies the sheet now visible sheet and inserts at the end of all sheets
ActiveSheet.Name = "Taks_" & s
ActiveWorkbook.Sheets(Taks(Choice)).Visible = False 'Makes sheet hidden again'


which finds the hidden sheet ("Taks", duplicates it, renames it ("Taks_office") and hids it back again.




So I would like to find a code to replace the bold line in the code below.



Sub Anvendelse1()

Dim wb As Workbook: Set wb = ThisWorkbook
Dim NewName As String: NewName = ""
Dim sh As Worksheet
Dim st
Dim s As String
Dim areal As String
Dim number As String

number = 6


Dim Choice As Integer
Dim BuildingType As Variant
Dim Taks As Variant

BuildingType = Array("empty", "empty", "NKB", "NER", "HOT", "UDD", "BØR", "BUT", "LOG", "PRO", "Hospital", "FLEX")
Taks = Array("empty", "empty", "Taks_NKB", "Taks_NER", "Taks_HOT", "Taks_UDD", "Taks_BØR", "Taks_BUT", "Taks_LOG", "Taks_PRO", "tom", "tom") 'The first two are empty



Choice = Range("F" + number).Value

If Choice = 1 Then 'When you choose the empty cell'
'Checks if there is an existing tab for "type 1'
If IsEmpty(Sheets("Start").Cells(number, 7).Value) Then 'This is when there is not an existing tab'
'do nothing
Exit Sub

ElseIf Not IsEmpty(Sheets("Forside").Cells(number, 7).Value) Then 'There is an existing tab'
Msg = "This action overwrites an existing tab with associated matrix. If you continue, the tab's contents will be lost. Do you want to continue?"
Ans = MsgBox(Msg, vbYesNo)


If Ans = vbYes Then
Sheets(Range("G" + number).Value).Delete 'Deletes the existing sheet for "type 1"
Sheets(Range(Taks_"G" + number).Value).Delete
Sheets("Forside").Range("G" + number).ClearContents 'Deletes the name given for "type 1"
Sheets("Forside").Range("H" + number).Value = 1 'Sets given area to one to avoid trouble
Exit Sub


ElseIf Ans = vbNo Then
Exit Sub

End If

End If

End If

 

[Domenic]

With the following code, the sheet is deleted, if it exists. Otherwise, if it doesn't exist, the resulting error is simply ignored, and the execution of the code continues...

    On Error Resume Next
    Application.DisplayAlerts = False
    Sheets(Range(Taks_"G" + number).Value).Delete
    Application.DisplayAlerts = True
    On Error GoTo 0

Hope this helps!