With statement on many sheets

[Rob]

I would like to know how to use a single With statement to apply to about 15
sheets

The statement for one sheet is.....

With Sheet5
.EnableSelection = xlUnlockedCells
.ScrollArea = "A1:J75"
.protect
End With

Rob

 

[Guest]

Here is some basic code that will loop through all of the sheets in the
workbook. If you want to exclude some sheets, you can use an if statement.

For Each sht In ThisWorkbook.Worksheets
With sht
.EnableSelection = xlUnlockedCells
.ScrollArea = "A1:J75"
.Protect
End With
Next sht

Hope this helps.

Dan

 

[Rob]

Thanks Dan. That certainly helps.

But isn't there a way to include all the applicable sheets in the With line?
eg With Sheet1, Sheet2, Sheet3 etc. (in some format that works)

I'm sure I've seen something like that in some other code which I can't find
any more

Rob

 

[Dave Peterson]

How about just looping through the list of sheets:

dim mySheetNames as Variant
dim iCtr as long

mysheetnames = array("sheet1", "sheet 2", "another sheetname")

for ictr = lbound(mysheetnames) to ubound(mysheetnames)
with worksheets(mysheetnames(ictr))
.EnableSelection = xlUnlockedCells
.ScrollArea = "A1:J75"
.protect
end with
next ictr

 

[Guest]

15 sheets seemed like a lot to list individually. I was guessing that the 15
sheets were the majority of the sheets in the workbook so therefore it would
be more efficient to loop through all sheets, but exclude the few you need to
exclude. For instance:

Sub test()
For Each sht In ThisWorkbook.Worksheets
If (sht.Name <> "Sheet6") And (sht.Name <> "Sheet8") Then
With sht
.EnableSelection = xlUnlockedCells
.ScrollArea = "A1:J75"
.Protect
End With
Next sht
End Sub

If you are working with the code names for the sheets, use sht.codename
instead of sht.name.

What benefit will you get from listing all 15 sheet names? If you really
need to list them, is there some logic in how they are named/set-up that you
can use to easily identify them?

 

[Guest]

I left out the "end if". I also put the with outside the if statement...

Sub test()
For Each sht In ThisWorkbook.Worksheets
With sht
If (.Name <> "Sheet6") And (.Name <> "Sheet8") Then
.EnableSelection = xlUnlockedCells
.ScrollArea = "A1:J75"
.Protect
End If
End With
Next sht
End Sub

 

[Rob]

Thanks again Dan. I wasn't thinking too clearly and your method and logic
are just fine!

Rob

 

[Rob]

Thanks Dave. It was the array("sheet1", "sheet 2", "another sheetname") line
I was after.

Rob

 

[Rob]

Yahoo, I hadn't time to try your first code, but this should work! Thanks
Dan.

Rob