Originally Posted by isabelle

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