Deleting worksheet only replaces worksheet name with a zero "0" number.

Is somebody able to tell me what's wrong with this code here?

I'm simply trying to delete a worksheet in my activeworkbook based off of a variable name located in a cell below.

I feel confident that this should be working based on the way that I have written it, but it's not doing what it's supposed to.

Instead of completely deleting the worksheet, it's only deleting the "name" of the worksheet, and replacing it with zero "0".

Sub DelVarWs()

Dim sName As Integer, ws As Worksheet

On Error Resume Next
sName = ActiveCell.Offset(1, 0).value
Set ws = ThisWorkbook.Worksheets(sName)
    
Application.DisplayAlerts = False
ws.Select
ws.Delete
Sheet1.Select
Application.DisplayAlerts = True

End Sub

The code has in fact errors but hidden by the resume next.

Option Explicit

Sub DelVarWs()

Dim sName As String, ws As Worksheet

'On Error Resume Next
sName = ActiveCell.Offset(1, 0).Value
Set ws = ThisWorkbook.Worksheets(sName)
    
Application.DisplayAlerts = False

ws.Delete

Application.DisplayAlerts = True

End Sub

sName must match the name on a workbook;otherise you will get out of range error

Originally Posted by AB33

The code has in fact errors but hidden by the resume next.

Option Explicit

Sub DelVarWs()

Dim sName As String, ws As Worksheet

'On Error Resume Next
sName = ActiveCell.Offset(1, 0).Value
Set ws = ThisWorkbook.Worksheets(sName)
    
Application.DisplayAlerts = False

ws.Delete

Application.DisplayAlerts = True

End Sub

sName must match the name on a workbook;otherise you will get out of range error

I figured it out finally.

Basically my issue had to do with me not identifying a cell address selection at the beginning of my code, and then I had to modify it to do a For Each Worksheet function.

It's working great now. Thank you AB33 for giving me the rookie tip of commenting out the On Error so that I could better dig down into what was really going wrong.

Sub DelVarWs()

    Dim rng As Range, sName As String, ws As Worksheet
        
    Set rng = ActiveSheet.Buttons(Application.Caller).TopLeftCell 'These next two lines were the secret sauce
    rng.Select 'Sometimes, all it takes is a good nights sleep to come back and figure it all out
    
    sName = ActiveCell.Offset(1, 0).value
    Set ws = ThisWorkbook.Worksheets(sName)

    For Each ws In ThisWorkbook.Worksheets
        If ws.Name = sName Then
            Application.DisplayAlerts = False
            ws.Delete
            Application.DisplayAlerts = True
        End If
    Next ws

End Sub

You could also add exit for line in the sub once the code found the sheet so it will not loop unnecessarily.

Sub DelVarWs()

    Dim rng As Range, sName As String, ws As Worksheet
        
    Set rng = ActiveSheet.Buttons(Application.Caller).TopLeftCell 'These next two lines were the secret sauce
    rng.Select 'Sometimes, all it takes is a good nights sleep to come back and figure it all out
    
    sName = ActiveCell.Offset(1, 0).value
    Set ws = ThisWorkbook.Worksheets(sName)

    For Each ws In ThisWorkbook.Worksheets
        If ws.Name = sName Then
            Application.DisplayAlerts = False
            ws.Delete
            Application.DisplayAlerts = True
            exit for
        End If
    Next ws

End Sub

Originally Posted by AB33

You could also add exit for line in the sub once the code found the sheet so it will not loop unnecessarily.

Sub DelVarWs()

    Dim rng As Range, sName As String, ws As Worksheet
        
    Set rng = ActiveSheet.Buttons(Application.Caller).TopLeftCell 'These next two lines were the secret sauce
    rng.Select 'Sometimes, all it takes is a good nights sleep to come back and figure it all out
    
    sName = ActiveCell.Offset(1, 0).value
    Set ws = ThisWorkbook.Worksheets(sName)

    For Each ws In ThisWorkbook.Worksheets
        If ws.Name = sName Then
            Application.DisplayAlerts = False
            ws.Delete
            Application.DisplayAlerts = True
            exit for
        End If
    Next ws

End Sub

Ooh... great idea!

Thanks again AB33!