Problem with Copy and PasteSpecial (changing sheet name )-CodePudding

The below code copy rows from sheet "Opened" (based on condition ) and paste on another sheet "Closed" in the same workbook and there is another event code on sheet "Closed" to prevent change name of that sheet.

Problems:(1) If I run copy code while sheet "Opened" is activated ,I got Run-time error 1004 That name is already taken. this error related to sheet "closed". no such error if I run code while sheet "closed is" activated.

Sub Copy_PasteSpecial()

Dim StatusColumn As Range
Dim DestRng As Range
Dim Cell As Object

    Set StatusColumn = Sheets("Opened").Range("H3", Sheets("Opened").Cells(Rows.count, "H").End(xlUp))
    
        For Each Cell In StatusColumn
         If Cell.value = "Close" Then
            Set DestRng = Sheets("Closed").Range("A" & Rows.count).End(xlUp).Offset(1, 0)
            Cell.EntireRow.Copy
            DestRng.PasteSpecial xlPasteValuesAndNumberFormats
            DestRng.PasteSpecial xlPasteFormats
          End If 
    Next Cell
End Sub

And this code for Prevent Changing Sheet Name:

Private Sub worksheet_SelectionChange(ByVal Target As Excel.Range)
If ActiveSheet.name <> "Closed" Then
   ActiveSheet.name = "Closed"
End If
End Sub

This link for the real workbook: https://easyupload.io/7ftxik

CodePudding user response：

The issue is that ActiveSheet in

Private Sub worksheet_SelectionChange(ByVal Target As Excel.Range)
    If ActiveSheet.name <> "Closed" Then
        ActiveSheet.name = "Closed"
    End If
End Sub

is not the sheet the SelectionChange event is in. That would be Target.Parent

Private Sub worksheet_SelectionChange(ByVal Target As Excel.Range)
    If Target.Parent.Name <> "Closed" Then
        Target.Parent.Name = "Closed"
    End If
End Sub