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]

your first code is unprotecting the workbook, but to work on a protected sheet within that same code you must unprotect the worksheet
is Sheet1 protected ?

Perhaps ...

Private Sub Workbook_BeforeClose(Cancel As Boolean)
Dim k, LastRow
ActiveSheet.Unprotect Password:="pass"
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
ActiveSheet.Protect Password:="admin"
End Sub

 

[dmt32]

Hi,

Welcome to forum.

Your code unprotects the workbook but you also need to unprotect the worksheet you want to perform the delete row action on.



Staying with the code published, try this update & see if what you want does.

Private Sub Workbook_BeforeClose(Cancel As Boolean)
    Dim k As Long, LastRow As Long
    Dim wsDest As Worksheet
    
    ActiveWorkbook.Unprotect Password:="admin"
    
    Set wsDest = Worksheets("Archive")
    
    With Worksheets("Sheet1")
        .Unprotect Password:="pass"
        LastRow = .Range("A" & .Rows.Count).End(xlUp).Row
    For k = LastRow To 2 Step -1
        If .Cells(k, "G").Value = 0 Then
         .Cells(k, "G").EntireRow.Cut Destination:=wsDest.Range("A" & wsDest.Rows.Count).End(xlUp).Offset(1)
         .Cells(k, "G").EntireRow.Delete
        End If
    Next k
        .Protect Password:="pass"
    End With
    
    ActiveWorkbook.Protect Password:="admin"
End Sub

When deleting rows in For Next Loop you would start at the last row & work through to the first row.

This approach will work ok with small data sets but is not very efficient – there are alternative approaches that can be used that are much faster am sure others here will post such solutions.

Hope Helpful

Dave

 

[Yongle]

Should Worksheet_Change be triggered when you are deleting the rows?
- unless I am missing something it seems pointless

If you agree ....

(If possible) I prefer NOT to disable events with Application.EnableEvents = False
- if the code fails there is a danger that Events are not switched back on again etc

An alternative method is to tell VBA to exit Worksheet_Change immediately whilst Workbook_BeforeClose is running
(yes it is less efficient, but I find the method less of a pain!)

'At TOP of a standard module (like Module1) - ABOVE all procedures
Public IgnoreEvents As Boolean

'    FIRST LINE of Worksheet_Change
If IgnoreEvents Then Exit Sub

'    FIRST LINE of Workbook_BeforeClose
IgnoreEvents = True

'    LAST LINE of Workbook_BeforeClose
IgnoreEvents = False

 

[09Tina]

Both of sheets are protected, but when some data are written in Sheet1, unlocked cell becomes locked and protected. This is to prevent data manipulation.
Dave I tried it but Run time Error '1004' (Delete method of Range class failed) appeared again for DeleteRow line in code.

 

[Yongle]

your first code is unprotecting the workbook, but to work on a protected sheet within that same code you must unprotect the worksheet
is Sheet1 protected ?

Perhaps ...

Private Sub Workbook_BeforeClose(Cancel As Boolean)
Dim k, LastRow
ActiveSheet.Unprotect Password:="pass"
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
ActiveSheet.Protect Password:="admin"
End Sub

OOPS - in my haste, I forgot to unprotecct/protect the workbook
- please add it back in

 

[Yongle]

Both of sheets are protected, but when some data are written in Sheet1, unlocked cell becomes locked and protected. This is to prevent data manipulation.
Dave I tried it but Run time Error '1004' (Delete method of Range class failed) appeared again for DeleteRow line in code.

Just spotted that you also need to unprotect sheet "Archive"
- it too is being changed

 

[09Tina]

Worksheet_Change works when unlocked cell is filled with data, to locked that cell and protect sheet, but when I want to put data again, it will be possible because unfilled cells are unlocked.

 

[09Tina]

But now I see that, maybe, I don't have to use code Worksheet_Change for protect my cells, if I have protection trough Workbook_BeforeClose. Is that correct approach?

 

[09Tina]

It worked when i deleted the worksheet_change code.
Thank you very much, you have helped me a lot!