0

I have got below macro to paste values only and I have assisgned Ctl+V as a shortcut.

Sub PasteasValue()

Selection.PasteSpecial Paste:=xlPasteValues

End Sub

With this method, end user have issue of pasting data on hidden rows, so I amended the code to select only visible cells. Rows are hidden when filters are on.

Sub PasteasValue()

Selection.SpecialCells(xlCellTypeVisible).Select

Selection.PasteSpecial Paste:=xlPasteValues

End Sub

However, my spreadsheet is protected and now it will give me an error at first code.

I tried below code to unprotect and protect the sheet, but this will not work as I understand the copied data on the clipboard is deleted when you unprotect the sheet and it will give me an error at paste value level.

Sub PasteasValue()

Activesheet.Unprotect "1234"

Selection.SpecialCells(xlCellTypeVisible).Select

Selection.PasteSpecial Paste:=xlPasteValues

Activesheet.Protect "1234"

End Sub

Below is what I am trying to achieve

  • Copy paste as value
  • Paste only in visible cells
  • Keep the sheet protected
  • Code should work for all worksheets in this workbook (all worksheets have same password)

Is there any simple method to achieve above?

Please help.

Thanks,

Rik

rikin bhavsar
  • 13
  • 1
  • 6
  • There is no Copy in your code. Are you trying to convert the current Selection to Values, or Paste whatever is in the clipboard as Values to the current Selection? – chris neilsen Nov 24 '20 at 01:25
  • Solve one problem at a time, first the protection. With the `Workbook_Open` event ask the user to enter the password. Use the password to unprotect each sheet and immediately reapply protection with `UserInterfaceOnly = True`. After that your code can make changes but the user can't. Note that the `UserInterfaceOnly` property expires when the workbook is closed. Therefore you have to repeat the exercise whenever it is opened. – Variatus Nov 24 '20 at 01:30
  • @ChrisNeilsen copy operation is performed as normal i.e. Ctl+C. So Paste whatever is in the clipboard as values to the current selection is what I am trying to acheive. – rikin bhavsar Nov 24 '20 at 01:37
  • @Variatus It defeats the purpose if we have to repeat the excercise of userinterfaceonly = true every time workbook is opened. Workbook will be opened like 5-10 times in one day. Unless I misunderstood your solution, I dont see this as working. – rikin bhavsar Nov 24 '20 at 01:39
  • The code works invisibly within about a 100th of a second. It doesn't matter how many times a day it runs. Any problem that might crop up would exist because of your demands on protection. But you can't deal with these demands when you want to paste data. That's why I suggest to deal with protection first and separately. Rest assured that the way I described is the proper way, and bear in mind that your proposed code would run even more often. – Variatus Nov 24 '20 at 01:45
  • 1) Hard coding a password in your VBA is a security risk. Anyone with malicious intent can easily access it. That said, if you are willing to hard code it as you have in your post, you can replace your current line `Activesheet.Unprotect "1234"` with `Activesheet.Protect "1234", , , , TRUE` and delete your line `Activesheet.Protect "1234"` This sets the `UserInterfaceOnly` property. ...to be continued – chris neilsen Nov 24 '20 at 02:27
  • 2) You are correct that calling `Protect` or `Unprotect` clears the clipboard. But before you call one of those, you can get a reference to the range that was copied to the clipboard [using this answer](https://stackoverflow.com/a/60033558/445425) and use that instead of PasteSpecial. ... to be continued – chris neilsen Nov 24 '20 at 02:28
  • 3) `.SpecialCells(xlCellTypeVisible)` will often be a non-contiguous range. `PasteSpecial` does not work with that so you'll need an alternative to put the data down (Note: it's not clear to me what you want to do if the Copied range and the visible Selection range are different sizes. If you clarify that I might try and answer) – chris neilsen Nov 24 '20 at 02:28
  • @chrisneilsen Thank you for your comments. I am trying to copy single cell to many cell, I am fine if I get an error with many cells to many cells. Does this make sense? I will give your solution a try but it might be little complex for me to implement. Thank you again. – rikin bhavsar Nov 25 '20 at 01:27
  • @Variatus thank you for your comment. Will you possibly share your solution in a code? I dont fully understand how to implement this. Many Thanks. – rikin bhavsar Nov 25 '20 at 01:29
  • Sorry, I can't post code to set up your protection in response to your question here. – Variatus Nov 25 '20 at 01:47

0 Answers0