0

I'm interested in how to protect a sheet in excel if the data from another sheet is copied automatically and there is a button that should work with the protected copy data? My my data copy code looks like:

Sub CopyRow1()
    ActiveSheet.Unprotect Password:="pass"
    Workbooks.Open Filename:="C:\Users\Desktop\workbook1.xlsm"

    ThisWorkbook.Sheets("1").Range("A5:G5").Copy
    Workbooks("workbook1").Sheets("Sheet1").Range("A1048567:G1048567").End(xlUp).Offset(1, 0).PasteSpecial Paste:=xlPasteValues
    Application.CutCopyMode = False 'esp
    ActiveSheet.Protect Password:="pass"

End Sub

and my code for button is:

Sub archive
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
End Sub
aynber
  • 22,380
  • 8
  • 50
  • 63
Kristina Kelek
  • 1
  • 1
    What's the problem you're having? You may just need to change `ActiveSheet.Protect` to `Workbooks("Workbook1").Worksheets("Sheet1").Protect`? – BruceWayne Dec 11 '19 at 15:10
  • I did this, but I get error 1004 indicating that I am transferring data to a protected sheet, and when I press debug I am marked with this line in second code: `Sheets("Sheet1").Cells(k,"G").EntireRow.Cut Destination :=Sheets("Archive").Range("A" & Rows.Count).End(xlUp).Offset(1)` and I dont know how to avoid that error. – Kristina Kelek Dec 12 '19 at 06:42

1 Answers1

0

first open two sheets one to work and one to link link one sheet data to another sheet now on to link sheet go review tab ,on protect sheet ,ao small window will open , by default 2 ticks will appear , no further more ticks to be made , enter password say "1" , and on confirm enter 1 close the small window , now modify data entry sheet , you will see that in link sheet data has too changed ( i have practically checked and found operational )

shahzad afzal
  • 1