Error when deleting activeworksheet

[Diving_Dan]

I'm using the following code to copy data from one sheet to two other sheets and then delete the active sheet. The code works and does everything that it needs to do. The only issue is a get the following error at the very end once it has finished.

Run-time error '-2147221080 (900401a8)': Automation Error

I've run the code line by line and the error occurs when deleting the sheet. If I remove the lines either side for displaying alerts I don't get the error. I do get a warning message about permanently deleting the sheet which I can click and I don't get the error message but ideally I don't want that message box.

Is there any way around this?

Private Sub cmdDutyComplete_Click()

        ThisWorkbook.Unprotect Password:="****"

    Dim answer As Integer
    answer = MsgBox("You are about to complete this duty. This will remove this sheet. Do you want to continue?", vbYesNo + vbQuestion, "Complete Duty")
    If answer = vbYes Then
    
    Sheets("OT Dates Issued").Unprotect Password:="****"
    
    Dim FindString As String
    Dim Rng As Range
    FindString = ActiveSheet.Range("J2").Value
    If Trim(FindString) <> "" Then
        With Sheets("OT Dates Issued").Range("A:A")
            Set Rng = .Find(What:=FindString, _
                            After:=.Cells(.Cells.Count), _
                            LookIn:=xlValues, _
                            LookAt:=xlWhole, _
                            SearchOrder:=xlByRows, _
                            SearchDirection:=xlNext, _
                            MatchCase:=False)
            If Not Rng Is Nothing Then
                  
             Rng.Offset(0, 6).Value = WorksheetFunction.CountA(Range("H5:H24"))
             Rng.Offset(0, 7).Value = WorksheetFunction.Sum(Range("N5:N24"))
                
            Else
                MsgBox "Nothing found"
            End If
        End With
    End If
    Sheets("OT Dates Issued").Protect Password:="****"
    
    Dim iRow As Long
        Dim ws As Worksheet
        Dim ws2 As Worksheet
        Dim z As Control
        
        Set ws = Worksheets("Overtime Data")
        Set ws2 = ActiveSheet
        ws.Unprotect Password:="****"

        iRow = ws.Cells(Rows.Count, 1).End(xlUp).Offset(1, 0).Row
            
            Dim rng2 As Range, cell As Range
            Set rng2 = Range("D5:D24")
            
            For Each cell In rng2
            
            If cell <> "" And UCase(cell) <> UCase("RESERVE LIST") Then
            
            ws.Cells(iRow, 1).Value = ActiveSheet.Range("$M$2:$Q$2").Value
            ws.Cells(iRow, 2).Value = ActiveSheet.Range("$M$2:$Q$2").Value
            ws.Cells(iRow, 3).Value = ActiveSheet.Range("$M$2:$Q$2").Value
            ws.Cells(iRow, 4).Value = ActiveSheet.Range("R2").Value
            ws.Cells(iRow, 5).Value = ActiveSheet.Range("F2").Value
            ws.Cells(iRow, 6).Value = cell.Offset(0, 9).Value
            ws.Cells(iRow, 7).Value = cell.Offset(0, 3).Value
            ws.Cells(iRow, 8).Value = cell.Offset(0, 4).Value
            ws.Cells(iRow, 9).Value = cell.Offset(0, 5).Value
            ws.Cells(iRow, 10).Value = cell.Offset(0, 6).Value
            ws.Cells(iRow, 11).Value = cell.Offset(0, 10).Value
            ws.Cells(iRow, 12).Value = cell.Offset(0, 11).Value
            
            iRow = iRow + 1
            End If
            Next cell
    
    ws.Protect Password:="****"
    Application.DisplayAlerts = False
    Sheets("Overtime Menu").Select
    ws2.Delete
    Application.DisplayAlerts = True
    
    End If
    ThisWorkbook.Protect Password:="****"
End Sub

 

[6StringJazzer]

What sheet is the active sheet at the point where you execute this line of code:

        Set ws2 = ActiveSheet

Generally, managing things by using ActiveSheet is not a good practice because it's hard to keep track of what's active at any given point in the code, and sometimes the code cannot control what's active. That may not be your problem but it is better to explicitly qualify sheet references. (The exception would be a general-purpose macro run from the Ribbon.)

 

[Diving_Dan]

It is still the active sheet. I used active sheet as the name of the sheet could be anything so I need the code to work no matter what the name of the worksheet is.

I may be able to get around this as the name of the worksheet is "J2" on every sheet that might run this code. It is used towards the top of the code

FindString = ActiveSheet.Range("J2").Value

FindString is the name of the worksheet.

How would I add this to the code to delete the sheet?

Worksheet("FindString").delete ????