Auto filter and protection with many sheets

[rob nobel]

Hi all,
The following procedure allows auto filter on protected sheets.
If I have many thus sheets, do I need to replicate all these lines or can I
include all their names somehow within this procedure?
With Worksheets("Tax Invoice records")
.EnableAutoFilter = True
.Protect Password:="password", _
Contents:=True, UserInterfaceOnly:=True
End With
Thanks,
Rob

 

[Dave Peterson]

If it's all the worksheets in the workbook, you won't need the names of any:

dim wks as worksheet
for each wks in activeworkbook.worksheets
with wks
.enableautofilter = true
.protect....
end with
next wks

(if all the passwords are the same, too!)

or if it's a subset of worksheets

dim wks as worksheet
for each wks in worksheets(array("sheet1","sheet2","sheet3"))
with wks
.enableautofilter = true
.protect....
end with
next wks

(same warning about the same password, too.)

I've seen some posts by Tom Ogilvy that say excel can get confused if this is in
your workbook_open event. Tom suggests selecting/activating the worksheet
before fiddling with the protection:

dim wks as worksheet
for each wks in worksheets(array("sheet1","sheet2","sheet3"))
with wks
.activate 'or .select
.enableautofilter = true
.protect....
end with
next wks

 

[rob nobel]

Hi Dave,
Just what I needed.
Thanks again.
Rob

If it's all the worksheets in the workbook, you won't need the names of any:

dim wks as worksheet
for each wks in activeworkbook.worksheets
with wks
.enableautofilter = true
.protect....
end with
next wks

(if all the passwords are the same, too!)

or if it's a subset of worksheets

dim wks as worksheet
for each wks in worksheets(array("sheet1","sheet2","sheet3"))
with wks
.enableautofilter = true
.protect....
end with
next wks

(same warning about the same password, too.)

I've seen some posts by Tom Ogilvy that say excel can get confused if this is in
your workbook_open event. Tom suggests selecting/activating the worksheet
before fiddling with the protection:

dim wks as worksheet
for each wks in worksheets(array("sheet1","sheet2","sheet3"))
with wks
.activate 'or .select
.enableautofilter = true
.protect....
end with
next wks