Unprotect and reprotect worksheet

[Jo4x4]

Hi everybody,

I am currently using the following code to copy certain data to another worksheet. This works fine if the destination sheet is not protected. What code do I need to add to unprotect and then reprotect the destination sheet?

Private Sub Worksheet_Change(ByVal Target As Range)

    Const sOrig As String = "Results" '<- Base sheet
    Const sNew As String = "Published results"  '<- Destination sheet
    Const CopyCols As String = "b:L"  '<- Columns to copy
    Const sCopyRows As String = "3:60" '<- Rows to copy
    
    With ActiveSheet
        If .Name = sOrig Then
            Application.ScreenUpdating = False
            
            Intersect(.UsedRange, .Columns(CopyCols), .Rows(sCopyRows)).Copy _
                Destination:=Sheets(sNew).Range("B3")
            Sheets(sOrig).Select
            Application.ScreenUpdating = True
        End If
    End With
End Sub

Thanks
Jo

Win XP, Office 2007

 

[jbeaucaire]

By adding:

            Sheets(sNew).Unprotect "Password"
            Intersect(.UsedRange, .Columns(CopyCols), .Rows(sCopyRows)).Copy _
                Destination:=Sheets(sNew).Range("B3")
            Sheets(sNew).Protect "Password"

 

[Jo4x4]

Thanks!