Having issue with Activesheet.Unprotect allowing all VBA code to run.

[Cyndie_Birdsong]

I have some macros on a sheet that is protected as it's set up like a user form, which I have protected so it doesn't get messed up by the various users. I've unlocked the cells they can modify, and protected the sheet to allow for other users to select unlocked cells. I created the macro based on recording my keystrokes via Excel's Macro Recorder, and it works fine if I don't protect the worksheet at all. Once I added the protection, it always hangs up at the Selection.PasteSpecial line. 

'Unprotect the worksheet
ActiveSheet.Unprotect
AllowFormattingCells = True
'
Range("AR5").Select
Selection.Copy
Range("G5:AC21").Select
Selection.PasteSpecial Paste:=xlPasteFormulas, Operation:=xlNone, _
SkipBlanks:=False, Transpose:=False

[#2]

@Cyndie_Birdsong 

You can unprotect the worksheet, perform actions that are only allowed in an unprotected sheet, then protect the sheet again:

    'Unprotect the worksheet
    ActiveSheet.Unprotect
    ' Copy and paste formulas
    Range("AR5").Copy
    Range("G5:AC21").PasteSpecial Paste:=xlPasteFormulas
    ' Protect the sheet again
    ActiveSheet.Protect AllowFormattingCells:=True

[#3]

@Cyndie_Birdsong 

I believe @HansVogelaar has provided the solution to your problem, but you might like to take a further step beyond macro recorder.  Unless it is important to you to transfer the font colour, size, cell colour, conditional formats, protection etc., you could simply transfer the values (or, I think in this case, the formula)

    'Unprotect the worksheet
    ActiveSheet.Unprotect
    'Copy formulas
    Range("G5:AC21") = Range("AR5").FormulaR1C1
    ' Protect the sheet again
    ActiveSheet.Protect AllowFormattingCells:=True

Personally, I would also use defined Names, just in case you wish to extend the Range "G5:AC21" or someone moves the cell "AR5".  It is very easy to forget to adjust the VBA otherwise.

[#4]

But that's what I'm doing, I have ActiveSheet.Unprotect at the beginning of that sub & ActiveSheet.Protect at the end. Are you saying to eliminate this part?
Operation:=xlNone, _
SkipBlanks:=False, Transpose:=False

There is more code in that sub but this was the line it was hanging up at..

[#5]

Thanks. I do have the Activesheet.unprotect at the beginning and the copy needs to be a PasteSpecial, Formula only, not values. It's just hanging up at that line I mentioned above.

[#6]

And the AR5 cell is behind protection so they can't move it. The main reason I want to protect the sheet is so users can't move anything, only fill in unlocked cells. thanks..

[#7]

@Cyndie_Birdsong 

I would have expected your code to stop at

AllowFormattingCells = True

since that isn't valid by itself.

[#8]

thanks - so that's supposed to go after I re-protect the sheet? I do have them unlocked via the Format Cells settings, so do I even need that part? I really just need to unlock the protection to run the code and then re-protect at the end. I also tried adding UserInterFaceOnly = true but that didn't work either. I'm sure it's something stupid that I'm missing.. 😉

[#9]

@Cyndie_Birdsong 

Have you tried the code posted by Peter Bartholomew or by me?

[#10]

I was finally able to get the code working - for anyone interested, I left the unprotect/protect code out of all the macros and only put it on the main VBA code for the workbook, and now it works great. Thanks all for your help!

[#11]

@Cyndie_Birdsong 

I see that you resolved your issue, but I would like to add information to your solution:

Maybe this is an official description of your issue.

VBA code "behind" a worksheet or a workbook may not work in Excel - Microsoft Support