Paste [as value] the already copied data [from another excel] to active cell

[Mehmud Khan]

Hello there,

Good day!




Due to some reason I have locked all cells in my excel (with pw = "12345") , now I want to paste (copied from another excel) column data only through VBA button to my excel's active cell.

I have used below VBA, which works only for one column but when I used for another column then getting below error;

>>> You have noted many times <<<
>>> use code - tags <<<​

Worksheets ("Sheet1"). Unprotect "12345"
ActiveCell.PasteSpecial xlPasteValues
Worksheets ("Sheet1"). Protect "12345"

Error getting;




Anyone has any clue ??? Why this error occurred??


Thanks in advance....

regards,
-Khan

 

[Mehmud Khan]

Also would like to add, if I remove the macro line [Worksheets ("Sheet1"). Protect "12345"] then everything works fine, but as I mentioned I don't want to keep unlock the cells. I only want users to fill the data from button only and not by using Cntl+V,

thanks and regards.

 

[Marc L]

Hello, as explained in VBA help - a must read ! - try :​

Worksheets("Sheet1").Protect "12345", , , , True
ActiveCell.PasteSpecial xlPasteValues

 

[Mehmud Khan]

Dear Marc,

Thanks for your reply!

Also thanks for recognizing me, so sorry for incorrect code tag, in future I'll more careful!

regarding the above code which you have given, seems incomplete.

Request you to review below;

1. The excel worksheet is already locked,
>>>>>first line of VBA is to unlock with pw "12345"


2. Column is already copied in clipboard, then,
>>>>>second line of VBA is to paste in active cell as value

3. After pasting the data, the workfile must again locked (to avoid Cntl+V or manual inputs)
>>>>>In thirt line, the VBA must again lock the worksheet


Also, I tried with your provided macro but I am getting same error.

Thanks in advance.

 

[Marc L]

1. The excel worksheet is already locked,
>>>>>first line of VBA is to unlock with pw "12345"

Yes but according to VBA help - a M U S T read ! - it unprotects the worksheet only for the VBA procedure.​

​

The reason why I do not think it's a VBA issue, try the same manually on the same destination cell …​

​