Unprotect code stops other code working

[Fellowe]

Hi All

I have the following code that copies data, opens another workbook, selects a sheet and pastes onto the next available row. It works beautifully.

Sub Submit()If MsgBox("Are you sure you want to submit data?", vbYesNo) = vbNo Then
Exit Sub
Else
MsgBox ("If you need to make changes please submit again.")
Sheets("Sheet1").Range("A3:S3").Copy
Workbooks.Open Filename:="N:\Location\File.xlsm", Password:="password"
Sheets("Submitted Data").Activate
With Sheets("Submitted Data").Range("A" & Rows.Count).End(xlUp).Offset(1)
    .PasteSpecial Paste:=xlPasteColumnWidths
    .PasteSpecial Paste:=xlPasteValues
End With
Workbooks("File.xlsm").Close SaveChanges:=True
End If
End Sub

However, I need to have the "Submitted Data" sheet protected so I added code to unprotect the sheet, paste the data and then protect the sheet again as below.

Sub Submit()If MsgBox("Are you sure you want to submit data?", vbYesNo) = vbNo Then
Exit Sub
Else
MsgBox ("If you need to make changes please submit again.")
Sheets("Sheet1").Range("A3:S3").Copy
Workbooks.Open Filename:="N:\Location\File.xlsm", Password:="password"
Sheets("Submitted Data").Activate
[COLOR=#ff0000]ActiveSheet.Unprotect "password"[/COLOR]
With Sheets("Submitted Data").Range("A" & Rows.Count).End(xlUp).Offset(1)
    .PasteSpecial Paste:=xlPasteColumnWidths
    .PasteSpecial Paste:=xlPasteValues
End With
[COLOR=#ff0000]ActiveSheet.Protect "password"[/COLOR]
Workbooks("File.xlsm").Close SaveChanges:=True
End If
End Sub

The sheet gets unprotected but I get Run-Time error '1004' PasteSpecial method of Range class failed.

If I remove the protection and the associated code, then it works but I lose the protection.

Any ideas on what is going wrong?

Thanks

 

[Fellowe]

Small update after a bit of digging

I have removed the protect and unprotect lines and added the following code to run when File.xlsm opens

Private Sub Workbook_Open()Sheets("Submitted Timesheets").Protect Password:="shergar", UserInterfaceOnly:=True
End Sub

However, I still get the same error when the code attempts to paste the data.

 

[Fellowe]

Whoops. Looks like I made a booboo in that last reply and I'm not allowed to edit it.

I have the following code running when File.xlsm opens so I shouldn't need to have code that unprotects and then protects again after my code is run.

Private Sub Workbook_Open()
Sheets("Submitted Data").Protect Password:="password", UserInterfaceOnly:=True
End Sub

I'm getting Run-Time error '1004' PasteSpecial method of Range class failed at the point below.

[COLOR=#333333].PasteSpecial Paste:=xlPasteColumnWidths[/COLOR]

This code works if the sheet is unprotected (not an option) but my understanding is that because I'm using UserInterfaceOnly:=True, the protection shouldn't be an issue since a macro should be able to run freely on a protected sheet as long as this is in place.