Discrepancy between function Argument and cell display

[Michael Simpler]

I have two sheets in a workbook. One is protected accept about two dozen cells.  The other is not protected.  When I run a macro to copy and paste 19 cells from the unprotected sheet to 19 cells in the protected sheet, 8 of the pastes go to unprotected cells to allow the user to edit those cells.  After the macro runs, the 8 cells are protected.

I unprotect the sheet, format the 8 cells as unlocked AGAIN, save, protect sheet, the cells are unlocked as they should be, until I run the macro.

Private Sub Workbook_Open()
'    Application.ExecuteExcel4Macro "SHOW.TOOLBAR(""Ribbon"",False)"
'    Application.DisplayFormulaBar = False
'    Application.DisplayStatusBar = Not Application.DisplayStatusBar
'    ActiveWindow.DisplayWorkbookTabs = False
    Sheets("user").Protect Password:="1goodjob", _
    UserInterFaceOnly:=True
End Sub

Any help would be greatly appreciated!

Thanks, Mike

See more conversations labeled with:

• Excel Desktop
• Need Help

Add Tag...

[Michael Simpler]

I have two sheets in a workbook. One is protected accept about two dozen cells.  The other is not protected.  When I run a macro to copy and paste 19 cells from the unprotected sheet to 19 cells in the protected sheet, 8 of the pastes go to unprotected cells to allow the user to edit those cells.  After the macro runs, the 8 cells are protected.

I unprotect the sheet, format the 8 cells as unlocked AGAIN, save, protect sheet, the cells are unlocked as they should be, until I run the macro.

Private Sub Workbook_Open()
'    Application.ExecuteExcel4Macro "SHOW.TOOLBAR(""Ribbon"",False)"
'    Application.DisplayFormulaBar = False
'    Application.DisplayStatusBar = Not Application.DisplayStatusBar
'    ActiveWindow.DisplayWorkbookTabs = False
    Sheets("user").Protect Password:="1goodjob", _
    UserInterFaceOnly:=True
End Sub

Any help would be greatly appreciated!

Thanks, Mike

[#3]

The Locked property is part of the cell formatting, so if you do a plain copy and paste, the target cells receive all formatting of the source cells. Try doing a paste special formulas (or values, depending on your needs) instead.

[#4]

Thank you sooo much! Special paste works!

[#5]

Thanks Jan! paste special worked great!