Delete a specific sheet depending on a value of a cell

[Kayslover]

Hi,

I would like to delete sheet named Sheet5 if the value of H2 in sheet named Formula is 4.

I intend to use this in a macro I use.

Thanks in anticipation

 

[LazyBug]

Hello!

Sub Kayslover()
Dim sh As Worksheet
'Application.DisplayAlerts = 0 'uncomment these commented lines to delete sheet without confirmation
    On Error Resume Next
    
    For Each sh In Worksheets
        If ThisWorkbook.Sheets("Formula").Range("H2") = 4 Then Sheets("Sheet5").Delete
    Next sh
'Application.DisplayAlerts = 1
End Sub

 

[Peter_SSs]

@LazyBug
There is no need/reason to cycle through all the worksheets. You loop does not use the variable sh within it and if there happened to be 50 sheets and
- 'Formula' H2 was 4, the loop would be deleting or trying to delete the same worksheet 50 times
- 'Formula' H2 was not 4 the loop would still check that same cell 50 times and each time decide no deletion was needed.

My suggestion

Sub CheckAndDelete()
  If Sheets("Formula").Range("H2").Value = 4 Then
    Application.DisplayAlerts = False
    On Error Resume Next
    Sheets("Sheet5").Delete
    On Error GoTo 0
    Application.DisplayAlerts = True
  End If
End Sub

 

[LazyBug]

@Peter_SSs, thank you, I'm very appreciate your explanation!
I admire your skills so much and I'm glad to learn from such masters at the forum.

 

[Kayslover]

Lazyboy, Peter_SSs,

Thank you both for your solutions. I am a novice, and have a question.

The fact we know that we only want to delete sheet called Sheet5, why do we have to have a loop to cycle throught the whole workbook?

Another question I have, having delete the sheet5, can I have a message that says "5th Sheet deleted" and if sheet5 has not been deleted, then a message "5th Sheet does not require deleting".

Thanks in anticipation.

 

[Peter_SSs]

why do we have to have a loop to cycle throught the whole workbook?

We don't. That is exactly what I wrote in post #3

There is no need/reason to cycle through all the worksheets.

My code in that post does not cycle through the whole workbook.

 

[Kayslover]

Peter_SSs,

Thanks for the prompt response and explaination.

What about:-

Having deleted the sheet5, can I have a message that says "5th Sheet deleted" and if sheet5 has not been deleted, then a message "5th Sheet does not require deleting".

 

[Peter_SSs]

Is this what you mean?

Sub CheckAndDelete()
  If Sheets("Formula").Range("H2").Value = 4 Then
    Application.DisplayAlerts = False
    On Error Resume Next
    Sheets("Sheet5").Delete
    On Error GoTo 0
    Application.DisplayAlerts = True
    MsgBox "Sheet5 deleted"
  Else
    MsgBox "Sheet5 not deleted"
  End If
End Sub

 

[Kayslover]

Peter_SSs,

Perfect, appreciate your assistance.

 

[Peter_SSs]

You're welcome. Glad we could help.