With statement on many sheets
 

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

With statement on many sheets
 

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" wrote:

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

With statement on many sheets
 

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


"Dan Hatola" wrote in message
...
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" wrote:

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

With statement on many sheets
 

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



Rob wrote:

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

--

Dave Peterson

With statement on many sheets
 

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?

"Rob" wrote:

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


"Dan Hatola" wrote in message
...
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" wrote:

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

With statement on many sheets
 

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

"Dan Hatola" wrote:

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?

"Rob" wrote:

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


"Dan Hatola" wrote in message
...
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" wrote:

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

With statement on many sheets
 

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

Rob


"Dan Hatola" wrote in message
...
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?

"Rob" wrote:

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


"Dan Hatola" wrote in message
...
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" wrote:

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

With statement on many sheets
 

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

Rob

"Dave Peterson" wrote in message
...
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



Rob wrote:

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

--

Dave Peterson

With statement on many sheets
 

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

Rob

"Dan Hatola" wrote in message
...
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

"Dan Hatola" wrote:

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?

"Rob" wrote:

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


"Dan Hatola" wrote in message
...
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" wrote:

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