CODEWITHSUNDEEP
excelvbacopy-paste
user14964947
user14964947

Reputation: 1

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

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?

Upvotes: 0

Views: 489

Answers (1)

Mayur Pathak
Mayur Pathak

Reputation: 84

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

Upvotes: 2

Related Questions