Code for multiple worksheets

[Paul S]

I am using excel 2000

How can I make the following code, saved in the This Workbook modul
available to

either all worksheets in the workbook

or specifically an additional worksheet called "Commission Achievement
as well as the "Missing Data" worksheetin my workbook

The code allows filterring on protected sheets

Private Sub Workbook_Open()
'check for filter, turn on if none exists
With Worksheets("Missing Data")
If Not .AutoFilterMode Then
.Range("A3").AutoFilter
End If
.EnableAutoFilter = True
.Protect _
Contents:=True, UserInterfaceOnly:=True
End With
End Sub


Thanks

Pau

 

[Dave Peterson]

One way:

Option Explicit
Private Sub Workbook_Open()

Dim iCtr As Long
Dim WKSNames As Variant

WKSNames = Array("Missing data", _
"Commission Achievement")

For iCtr = LBound(WKSNames) To UBound(WKSNames)
With Me.Worksheets(WKSNames(iCtr))
If Not .AutoFilterMode Then
.Range("A3").AutoFilter
End If
.EnableAutoFilter = True
.Protect Contents:=True, UserInterfaceOnly:=True
End With
Next iCtr
End Sub

If you really wanted all the worksheets in that workbook:

Option Explicit
Private Sub Workbook_Open()

Dim wks As Worksheet

For Each wks In Me.Worksheets
With wks
If Not .AutoFilterMode Then
.Range("A3").AutoFilter
End If
.EnableAutoFilter = True
.Protect Contents:=True, UserInterfaceOnly:=True
End With
Next wks
End Sub