I have a protected worksheet which users can enter data into the unprotected
areas. To try to prevent these areas becoming corrupted by formats being
copied in from other worksheets, I have added the following deactivate change
event to €œdatasheet€� (tried at first to use a cell change event, but it
became too complex):

Private Sub Worksheet_Deactivate()
Dim mynewSheet As String

Application.EnableEvents = False
Application.ScreenUpdating = False
mynewSheet = ActiveSheet.Name

Worksheets("formatsheet").Activate €˜very hidden copy of datasheet formats
Application.Goto reference:="entiresheet"
Selection.Copy
Worksheets("datasheet").Activate
ActiveSheet.Unprotect
Range("a1").Select
Selection.PasteSpecial Paste:=xlPasteFormats
Range("a1").Select
ActiveSheet.Protect DrawingObjects:=True

Worksheets(mynewSheet).Activate

Application.EnableEvents = True
Application.ScreenUpdating = True

End Sub

This works fine when datasheet is unprotected and without the
Activesheet.Unprotect/Protect statements, but the PasteSpecial fails when the
sheet is protected and with these statements included as above. Can anyone
tell me why?