How to automatically copy from sheet to sheet, when sheets have to be protected?

[09Tina]

I'm interested in how to automatically copy from sheet to sheet, when sheets have to be protected. When i use the code written below in unprotected sheets it works perfectly. but when I use this code in combination

Private Sub Workbook_BeforeClose(Cancel As Boolean)
ActiveWorkbook.Unprotect Password:="admin"
Dim k, LastRow
LastRow = Sheets("Sheet1").Range("A" & Rows.Count).End(xlUp).row
For k = 2 To LastRow
    If Sheets("Sheet1").Cells(k, "G").Value = 0 Then
    Sheets("Sheet1").Cells(k, "G").EntireRow.Cut Destination:=Sheets("Archive").Range("A" & Rows.Count).End(xlUp).Offset(1)
    Sheets("Sheet1").Cells(k, "G").EntireRow.Delete
    End If
Next k
ActiveWorkbook.Protect Password:="admin"
End Sub

But when I use this code in combination with ProtectCell code I get Error 1004 that says that I have to delete a DeleteEntireRow line in the code, but when I delete it, an error 1004 is shown again and says that I can't work in a protected sheet.

Private Sub Worksheet_Change(ByVal Target As Range)
Dim cl As Range
ActiveSheet.Unprotect Password:="pass"
For Each cl In Target
    If cl.Value <> "" Then
    cl.Locked = True
    End If
Next cl
ActiveSheet.Protect Password:="admin"
End Sub

Please help!

 

[Yongle]

glad we helped
- but I am surprised that deleting Worksheet_Change was your solution

 

[dmt32]

glad we helped
- but I am surprised that deleting Worksheet_Change was your solution

I'm not, I forgot to include Application.EnableEvents = False in my code to prevent that event triggering - but no worries, we both helped resolve new OPs issue which is a good result

Dave

 

[09Tina]

You were right, it was too changed and complicated for exel recognize that the sheet was unprotected in line before, because of code Worksheet_Change wich worked with locked and unlocked cells andprotected and unprotected sheets, i think. And yes, You helped me a lot guys, thank You very much.