VBA to unprotect all sheets run my code and then protect.

[pete4monc]

Hi guys

I'm hoping someone can help me, I've got some code which runs perfect (from button click) but I need to password protect the sheet so no one fiddles oops I mean changes anything.
Problem is when the sheets are protected the vba doesn't run? VBA on one sheet will populate cells on another.

I have done this vba for one sheet and it works

Sheets("CustomerService").Unprotect "Pete"
MY CODE HERE
Sheets("CustomerService").Protect "Pete"

So I modified it to run on all sheet names and it fails? Any ideas?

Sheets("PCD" , "Sales", "PCDPlanning",  "CustomerService1").Unprotect "Pete"
MY CODE HERE
Sheets("PCD" & "Sales" & "PCDPlanning" & "CustomerService1").Protect "Pete"

Thanks in advance guys.

 

[mumps]

Delete your 2 lines of code that protect/unprotect and try:

With ws
    .Unprotect Password:="Pete"
    Range("A" & x & ":G" & x).Copy .Cells(.Rows.Count, "A").End(xlUp).Offset(1)
    .Protect Password:="Pete"
End With

 

[pete4monc]

Delete your 2 lines of code that protect/unprotect and try:

With ws
    .Unprotect Password:="Pete"
    Range("A" & x & ":G" & x).Copy .Cells(.Rows.Count, "A").End(xlUp).Offset(1)
    .Protect Password:="Pete"
End With

Thanks Mumps, that worked a treat!!

Thanks to everyone who tried to help. @mumps @dmt32 @mikerickson

 

[mumps]

You are very welcome.

 

[Borekch]

Hi,
I used this code for protecting of all Worksheets, but now if I tried to click on the button in Tools for Unprotect of sheet, then the sheet was unprotected without password. If I click on the button for unprotecting of Workbook, then the system wants password. Thanks