Making changes to most worksheets?

[jonco]

I have a workbook where I want to make some cell protection changes on most
of the sheets. There are about 100 sheets so I'd rather not do them
individually.

I want to unprotect certain cells so that when the sheet is 'protected'
these cells can still be used for entry and pasting.

Here's what I have so far, but it's not working: (copied from another
workbook and modified)

'TempProtect ()

Dim ws As Worksheet
For Each ws In ThisWorkbook.Worksheets

Range("I2,J2,L2,N2,O22,A35:T36,A35").Select
IF Worksheet.name = "Index" Then Next (I want it to skip this
worksheet)
IF Worksheet.name = "Trans" Then Next (I want it to skip this
worksheet also)
IF Worksheet.name = "Customers" Then Next (I want it to skip this
worksheet too)
Selection.Locked = False
Selection.FormulaHidden = False
ActiveSheet.Protect DrawingObjects:=True, Contents:=True,
Scenarios:=True

Next

End Sub

Any help you can give me is appreciated.
Jonco

 

[tony h]

try this:


Sub TempProtect()

Dim ws As Worksheet
Dim rng As Range

For Each ws In ThisWorkbook.Worksheets
Select Case ws.Name
Case "Index", "Trans", "Customers"
'don't do anything
Case Else
Set rng = ws.Range("I2,J2,L2,N2,O22,A35:T36,A35")
rng.Locked = False
rng.FormulaHidden = False
ws.Protect DrawingObjects:=True, Contents:=True, Scenarios:=True
End Select

Next
MsgBox "done"
End Sub

regards

 

[Tim Williams]

'******************************
Sub TempProtect()

Dim ws As Worksheet
Dim s As String

For Each ws In ThisWorkbook.Worksheets
s = ws.Name
If Not (s = "Index" Or s = "Trans" Or s = "Customers") Then
With ws.Range("I2,J2,L2,N2,O22,A35:T36,A35")
.Locked = False
.FormulaHidden = False
End With
ws.Protect DrawingObjects:=True, Contents:=True, _
Scenarios:=True

End If
Next ws
End Sub
'*****************************


Tim

 

[jonco]

I'm trying to make some changes to all but three sheets in amy workbook.
I got the following macro here, but it's still not working.

Any help will be appreciated.
I'm getting the following error:

Run-time Error: 1004 Unable to set the Locked property of the Range class

Sub TempProtect()
Dim ws As Worksheet
Dim rng As Range
For Each ws In ThisWorkbook.Worksheets
Select Case ws.Name
Case "Index", "Trans", "Customers"
'don't do anything
Case Else
Set rng = ws.Range("I2,J2,L2,N2,O22,A35:T36,A35")
rng.Locked = False ' ************************************ This is where
the error is
rng.FormulaHidden = False
ws.Protect DrawingObjects:=True, Contents:=True, Scenarios:=True
End Select
Next
MsgBox "done"
End Sub

Thanks for any help I can get.
Jonco

 

[tony h]

Is it all worksheets or just some?

do A ?ws.name in the immediate pane to find out.

I would guess it might be something to do with the worksheet alread
being protected.

Sorry I can't investigate further ... got work to d

 

[jonco]

I got it to work. I think the problem was that I was referencing the same
call twice in the range. Once I eliminated that it worked.

Thanks for responding.

Jon