0

Simple problem that I can't solve....

I use below VBA to paste a range of 100 cells, just to avoid any hassle and speed up the process. But I get an error.

ActiveSheet.Unprotect
Range("C6").Select

Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
    :=False, Transpose:=False
Application.CutCopyMode = False
Range("A1").Select
ActiveSheet.Protect

The error is in the "selection.pastespecial....", It seems that it happens because the sheet is protected and the cells are locked, and the "unprotect" code doesn't have time to react. If I run the macro again, it works, I guess this is because now the sheet is not protected anymore. If I unlock the cells it works fine, but I would prefer to have them locked.

Pᴇʜ
  • 56,719
  • 10
  • 49
  • 73
Nick
  • 142
  • 9
  • can we see more of the code. It probably has more to do with the use of activating and selecting than the unprotect. – Scott Craner Dec 02 '20 at 15:13
  • 1
    You might benefit from reading [How to avoid using Select in Excel VBA](https://stackoverflow.com/questions/10714251/how-to-avoid-using-select-in-excel-vba). Make sure to avoid `.Select` this can cause many strange issues. – Pᴇʜ Dec 02 '20 at 15:13
  • thank you for the help, I found a solution. @ScottCraner This is all code, as I said, it's only to speed up a boring task. I have a similar macro for copying the data – Nick Dec 02 '20 at 15:17
  • @Pᴇʜ I know, but for such a small macro I didn't think it would cause any problems – Nick Dec 02 '20 at 15:17
  • it cannot be all the code, there is no `.copy` line. – Scott Craner Dec 02 '20 at 15:18
  • @Nick It definitely could. The `Selection` can change at any time of the macro by a single user click as well as the `ActiveSheet` can change easily. It is very unreliable and might once run as expected and another time not. I highly recommend to fix that. – Pᴇʜ Dec 02 '20 at 15:19
  • @ScottCraner It can if it is manually copied. – Pᴇʜ Dec 02 '20 at 15:19
  • I have a macro that is basically "Range("A1:A100").copy and then I have this macro to paste it. It is 2 different workbooks and I don't like to "connect" them, that is why I have this setup. – Nick Dec 02 '20 at 15:24

1 Answers1

0

EDIT: This did not solve it! I decided to unlock the cells instead as a compromise.

EDIT 2: I also found the problem, when I unprotect the sheet, the range that was copied is lost. So I'm trying to paste something that doesn't exist, the solution was to unlock the cell and remove the "unprotect" part of the VBA.

Found a solution, by adding "activesheet.select" between the unprotect and "paste" I believe I delayed the macro enough to have time to unprotect.

ActiveSheet.Unprotect
ActiveSheet.Select
Range("C6").Select

Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
    :=False, Transpose:=False
Application.CutCopyMode = False
Range("A1").Select
ActiveSheet.Protect
Nick
  • 142
  • 9
  • Though this may work it is the long way to do this. There are more precise and less problematic methods that are cleaner and quicker. – Scott Craner Dec 02 '20 at 15:19
  • What do you mean? the macro is used on 30 different sheets, but they all look the same and I need to perfom the same action on all sheets. So, instead of writing 30 different macros, I use this one for all sheets – Nick Dec 02 '20 at 15:27
  • You could protect the sheets with `UserInterfaceOnly:=True`. – BigBen Dec 02 '20 at 15:40