Making changes to most worksheets?
 

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,O2:P2,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

Making changes to most worksheets?
 

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,O2:P2,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


--
tony h
------------------------------------------------------------------------
tony h's Profile: http://www.excelforum.com/member.php...o&userid=21074
View this thread: http://www.excelforum.com/showthread...hreadid=557605

Making changes to most worksheets?
 

Thanks guys, I'll give these a try. I appreciate it.

Jonco


"jonco" wrote in message
. com...
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,O2:P2,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

Making changes to most worksheets?
 

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

--
tony
-----------------------------------------------------------------------
tony h's Profile: http://www.excelforum.com/member.php...fo&userid=2107
View this thread: http://www.excelforum.com/showthread.php?threadid=55760