VBA to unprotect and reprotect worksheets

[SaraB802]

Still getting my head around VBA, and now having problems with unprotect and protect. Below is the code I have ad it worked until we password protected the worksheets to stop the users deleting the formulas. have looked at loads of posts but can't seem to fit the solutions into my code below:

Public Sub Update_SLA_C_Event()
Dim lngCalc As XlCalculation
Dim wkb1 As Excel.Workbook, wkb2 As Excel.Workbook, wkb3 As Excel.Workbook, wkb4 As Excel.Workbook, wkb5 As Excel.Workbook, wkb6 As Excel.Workbook, wkb7 As Excel.Workbook, wkb8 As Excel.Workbook

With Application
.ScreenUpdating = False
lngCalc = .Calculation
.Calculation = xlManual
.EnableEvents = False
.EnableCancelKey = xlErrorHandler
End With

Set wkb1 = ThisWorkbook
Set wkb2 = Workbooks.Open("C:\Users\hp\Desktop\Trade Marketing\Central EvePro2.xlsm")
Set wkb3 = Workbooks.Open("C:\Users\hp\Desktop\Trade Marketing\Northern EvePro2.xlsm")
Set wkb4 = Workbooks.Open("C:\Users\hp\Desktop\Trade Marketing\Southern EvePro2.xlsm")
Set wkb5 = Workbooks.Open("C:\Users\hp\Desktop\Trade Marketing\Eastern EvePro2.xlsm")
Set wkb6 = Workbooks.Open("C:\Users\hp\Desktop\Trade Marketing\Sabah EvePro2.xlsm")
Set wkb7 = Workbooks.Open("C:\Users\hp\Desktop\Trade Marketing\Sarawak EvePro2.xlsm")
Set wkb8 = Workbooks.Open("C:\Users\hp\Desktop\Trade Marketing\Events EvePro2.xlsm")
Call wkb1.Sheets("SLA C_Event").Range("B16:H248").Copy
Call wkb2.Sheets("SLA C_Event ").Range("B16").PasteSpecial(Paste:=xlValues)
Call wkb3.Sheets("SLA C_Event ").Range("B16").PasteSpecial(Paste:=xlValues)
Call wkb4.Sheets("SLA C_Event ").Range("B16").PasteSpecial(Paste:=xlValues)
Call wkb5.Sheets("SLA C_Event ").Range("B16").PasteSpecial(Paste:=xlValues)
Call wkb6.Sheets("SLA C_Event ").Range("B16").PasteSpecial(Paste:=xlValues)
Call wkb7.Sheets("SLA C_Event ").Range("B16").PasteSpecial(Paste:=xlValues)
Call wkb8.Sheets("SLA C_Event ").Range("B16").PasteSpecial(Paste:=xlValues)

Application.CutCopyMode = False
Call wkb1.Sheets("SLA C_Event").Range("J17:L35").Copy
Call wkb2.Sheets("SLA C_Event").Range("J17").PasteSpecial(Paste:=xlValues)
Call wkb3.Sheets("SLA C_Event").Range("J17").PasteSpecial(Paste:=xlValues)
Call wkb4.Sheets("SLA C_Event").Range("J17").PasteSpecial(Paste:=xlValues)
Call wkb5.Sheets("SLA C_Event").Range("J17").PasteSpecial(Paste:=xlValues)
Call wkb6.Sheets("SLA C_Event").Range("J17").PasteSpecial(Paste:=xlValues)
Call wkb7.Sheets("SLA C_Event").Range("J17").PasteSpecial(Paste:=xlValues)
Call wkb8.Sheets("SLA C_Event").Range("J17").PasteSpecial(Paste:=xlValues)

With Application
.ScreenUpdating = True
.Calculation = lngCalc
.EnableEvents = True
.EnableCancelKey = xlInterrupt
End With

wkb2.Close SaveChanges:=True
wkb3.Close SaveChanges:=True
wkb4.Close SaveChanges:=True
wkb5.Close SaveChanges:=True
wkb6.Close SaveChanges:=True
wkb7.Close SaveChanges:=True
wkb8.Close SaveChanges:=True
End Sub

 

[Shadow123]

simple way to go would seem to be to unprotect the workbooks.. i think its this to do that, but i don't have the protect function on excel in the office (ugh don't ask)

 wkb1.Unprotect Password:="111111"
wkb1.Protect Password:="111111"

 

[Shadow123]

SOrry that was for workbooks, maybe

sub testme()
* dim myPWD as string
* myPwd = "x"
* activesheet.unprotect password:=myPWD

* 'do your stuff

* activesheet.protect password:=mypwd
end sub

 

[Festus Hagen]

Use the .Unprotect and .Protect methods ...

Sub yoursub()
YourWorksheet.Unprotect ...

Do your thing here ...

YourWorksheet.Protect ...
.Close ...
End sub

-Enjoy
fh : )_~

 

[SaraB802]

I get the codelines to use, thank you. But I am having problems where to put them in the code I already have. As i don't activiate a worksheets anywhere I tried to edit the code lines so it goes

Call wkb2.Sheets("SLA C_Event ").Unprotect("rubyrocks").Range("B16").PasteSpecial(Paste:=xlValues)

but get an unexpected end error
I guess I can put the protect lines I will put in before

wkb2.Close SaveChanges:=True

 

[Festus Hagen]

Set wkb8 = Workbooks.Open("C:\Users\hp\Desktop\Trade Marketing\Events EvePro2.xlsm")
wkb1.Sheets("SLA C_Event").Unprotect For each sheet to be modified
Call wkb1.Sheets("SLA C_Event").Range("B16:H248").Copy

Blah ... Blah ...

wkb1.Sheets("SLA C_Event").Protect
wkb2.Close SaveChanges:=True

-Enjoy
fh : )_~