Clearing data for locked cells or protected worksheet

[hobbes11]

I need to clear the below data except formula in sheet(invoice) after I copy data into sheet(report). The sheet (invoice) will be protected by locking some of the cells for any editing. If i protect the worsheet there is no way my macro can run.

In Column B7 - manual data input
Column B9 - drop down list
Colum B10:B13 - Vlookup

In Column F10 - today () formula
Column F11 & F12 - drop down list
Column F14 - manual data input

In column A18:A41 - drop down list
column B18:B41 - Vlookup formula
column C18:C41 & D18:D41 - drop down list
column E & F - manual data input
column F49 - drop down list

In Row 44:48 - manual data input

My current code:

Private Sub CmdClearData_Click()
'Remove data but not formulaAns = MsgBox("Have you update to report?", vbYesNo + vbQuestion)
If Ans = vbNo Then Exit Sub
Sheet1.Range("B7:B13").SpecialCells(xlCellTypeConstants).ClearContents
Sheet1.Range("F11:F15").SpecialCells(xlCellTypeConstants).ClearContents
Sheet1.Range("A19:F41").SpecialCells(xlCellTypeConstants).ClearContents
Sheet1.Range("A44:F48").SpecialCells(xlCellTypeConstants).ClearContents
End Sub

 

[mumps]

You can get your macro to run by having it unprotect the sheet at the beginning and then protecting it again at the end. For example:

Activesheet.Unprotect
'Your code here
Activesheet.Protect

 

[hobbes11]

your code works fine but when i run the macro, all my data in cells which I have locked are also deleted. ??

 

[mumps]

Unprotecting and protecting your sheet shouldn't make any difference in how the rest of the macro works. If it was working properly before, it should work properly after unprotecting and protecting the sheet. The only ranges that should be cleared are the ones you mentioned in your original post. Was your macro working properly when the sheet was originally unprotected?