this code will be more efficient if the number of sheets and cells is big

Sub test()
Dim oSh As Worksheet, Rng As Range, i As Integer, str1 As String, str2
As String
Dim sSh()
sSh = Array("staff", "manager", "director")

For Each oSh In Worksheets
str1 = Len(oSh.Name)
For i = LBound(sSh) To UBound(sSh)
str2 = Len(Application.Substitute(oSh.Name, sSh(i), ""))
If str1 < str2 Then
' MsgBox oSh.Name & " contains the word <" & sSh(i) & "" 'test
Select Case i
Case 0: Set Rng = Union(Range("M5"), Range("D14:E14"))
'staff sheet
Case 1: Set Rng = Union(Range("D9:E39"), Range("G44:I49"))
'manager sheet
Case 2: Set Rng = Range("B44:E49")
'director sheet
End Select
oSh.Range(Rng.Address).ClearContents
Set Rrg = Nothing
End If
Next i
Next
End Sub

--
isabelle

isabelle
thanks for that however i've been told today that the list of sheets may change AND be added to.
I was trying to have a sheet with a list of the sheets in to file, that i could flag as to clear. My problem is the sheet names can change as new staff come and go so the sheet names are not fixed.
Due to confidentially i can't upload a file. However i'm looking at taking those bits out so i can post an example.
hopefully it will be done late tomorrow.

kinda fixed it by using the following

Not pretty but will do the job for now.
thanx everyone