hi,

i hope that this example will be useful,

Sub test()
Dim oSh As Worksheet, Rng As Ranges, i As Integer
Dim sSh()

sSh = Array("staff", "manager", "director")

For Each oSh In Worksheets
For i = LBound(sSh) To UBound(sSh)
str1 = Len(Application.Substitute(oSh.Name, sSh(i), ""))
str2 = Len(oSh.Name)
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
End If
Next i
Next
Set Rrg = Nothing
End Sub

--
isabelle


Le 2012-11-07 09:04, y0rk1e72 a écrit :
Let me explain.
How do i get a macro/vba to look at a list of sheets in one worksheet
and depending on the data to the right of the name clear the correct
range(s).
for example list would look like
staff M5, D14:E14
manager D9:E39, G44:I49
director B44:E49

so using the above i'd need to look at the name, i.e staff and it would
clear M5, followed by the range D14:E14 then would look and the next
name which would be manager and clear D9:e39 followed by G44:I49. it
would then look at the next name in the list which using the above would
be director.

The sheets are hidden and have merged cells within the range to be
cleared.

is this even possible?
any help/pointers would be greatly appreciated.

also posted on
http://tinyurl.com/a7aoz8w
http://tinyurl.com/aofkj9c
http://tinyurl.com/cbks2kg