PasteSpecial

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

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?
 
If the deactivate event is in the DataSheet code module, try this:

Private Sub Worksheet_Deactivate()
On Error Resume Next
Set sh = ActiveSheet
Application.EnableEvents = False
Application.ScreenUpdating = False
With Worksheets("datasheet")
.Unprotect
Worksheets("FormatSheet") _
.Range("entiresheet").Copy
.Activate
.Range("A1") _
.PasteSpecial Paste:=xlPasteFormats
.Range("A1").Select
.Protect
End With
sh.Activate
Application.EnableEvents = True
Application.ScreenUpdating = True
End Sub
 
Thanks Tom. This sorted it.

Tom Ogilvy said:
If the deactivate event is in the DataSheet code module, try this:

Private Sub Worksheet_Deactivate()
On Error Resume Next
Set sh = ActiveSheet
Application.EnableEvents = False
Application.ScreenUpdating = False
With Worksheets("datasheet")
.Unprotect
Worksheets("FormatSheet") _
.Range("entiresheet").Copy
.Activate
.Range("A1") _
.PasteSpecial Paste:=xlPasteFormats
.Range("A1").Select
.Protect
End With
sh.Activate
Application.EnableEvents = True
Application.ScreenUpdating = True
End Sub
Click to expand...
 
Back
Top