Copy & Paste between sheets_ the destination sheet is a password protected

Question

I am trying to copy values from Sheet1 to Sheet2. Sheet2 is Password protected. I have added a Macro(for Auto color update based on cell values) to the Sheet2 whenever it activates.

Since Sheet2 is Protected, the Macro code will have to first Unprotect, do the changes and then Protect the Sheet.

Meanwhile, in the process, the copied data is getting lost and I am unable to copy and paste data from Sheet1 to Sheet2.

Can anyone please help me resolve this issue?

Two options that I can immediately think of... **1.** Do the unprotecting first and then copy. You should avoid doing anything between copy and paste. You may want to see [THIS](https://stackoverflow.com/questions/64872887/why-does-pastespecial-method-sometimes-throw-error-1004-and-other-times-not) **2.** Use `UserInterfaceOnly:=True` That ways you will not have to unprotect the worksheet. You can read about it in [Worksheet.Protect method (Excel)](https://learn.microsoft.com/en-us/office/vba/api/excel.worksheet.protect) — Siddharth Rout, Jan 08 '21 at 09:55
First open sheet 2 and provide the password, THEN do the copy & paste and finally re-password sheet 2. — Solar Mike, Jan 08 '21 at 09:56

score 2 · Answer 1 · edited Jan 08 '21 at 09:59

Unprotect the sheet first and then do the copy paste. Finally re-protect the sheet as shown below.

Sub CopyPaste()
    Set Source = ThisWorkbook.Worksheets("sheet1")
    Set Destination = ThisWorkbook.Worksheets("sheet2")

    Destination.Unprotect Password:="password"

    Source.Range("A1").Copy
    Destination.Range("A3").PasteSpecial Paste:=xlPasteFormats

    Destination.Protect Password:="password"
End Sub

Copy & Paste between sheets_ the destination sheet is a password protected

1 Answers1