global format copy to multiple worksheets

[John January]

I have 20 worksheets that will be protected. Each of the
worksheets areas to be protected are identical but some
of the data will be pulled from other sheets. So for
example, if I wanted to protect cells in A5:A20 and
B5:B36 and Q105:Q128 for 20 worksheets, is there a global
way to do that, or do I have to protect each one
individually?

 

[Ron de Bruin]

You can run a macro like this John

Sub test()
For Each sh In ThisWorkbook.Worksheets
With sh
.Cells.Locked = False
.Range("A5:A20 ,B5:B36 ,Q105:Q128 ").Locked = True
.Protect
End With
Next
End Sub

 

[Ken Wright]

Group all the sheets by clicking on the first, holding down SHIFT and then
clicking on the last. Now format the cells with the relevant protection and
they will all be changed. In order to protect the sheets though, you either
need to trawle through them all manually, or use VBA to get the job done:-

Following routines from JE McGimpsey

Public Sub ProtectAllSheets()
'Space allowed for insertion of a password
'Code lists every sheet with it's protection status
Application.ScreenUpdating = False
Const PWORD As String = ""
Dim wkSht As Worksheet
Dim statStr As String

For Each wkSht In ActiveWorkbook.Worksheets
With wkSht
statStr = statStr & vbNewLine & "Sheet " & .Name
wkSht.Protect Password:=PWORD
statStr = statStr & ": Protected"
End With
Next wkSht
MsgBox Mid(statStr, 2)
Application.ScreenUpdating = True
End Sub


Public Sub UnprotectAllSheets()
'Space allowed for insertion of a password
'Code lists every sheet with it's protection status
Application.ScreenUpdating = False
Const PWORD As String = ""
Dim wkSht As Worksheet
Dim statStr As String

For Each wkSht In ActiveWorkbook.Worksheets
With wkSht
statStr = statStr & vbNewLine & "Sheet " & .Name
wkSht.Unprotect Password:=PWORD
statStr = statStr & ": Unprotected"
End With
Next wkSht
MsgBox Mid(statStr, 2)
Application.ScreenUpdating = True
End Sub