VBA to clear multiple ranges from list of sheets

[y0rk1e72]

Having surched the web and other sites for two days i desperatly need some help.
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.

 

[Biz]

Provide all sheet names to be cleared . In addition, merged cells ranges to be cleared.

 

[y0rk1e72]

Provide all sheet names to be cleared . In addition, merged cells ranges to be cleared.

Biz
that's my problem 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.

 

[y0rk1e72]

Kinda solved it by using the following code..

Private Sub Worksheet_Activate()
Dim c As Range, msg As String, style As String, title As String
msg = "Do you want to clear your entries?" & vbCrLf & "click No to cancel"
style = vbYesNo + vbQuestion + vbDefaultButton2
title = "Action required"
response = MsgBox(msg, style, title)
If response = vbNo Then Exit Sub
Me.Protect "test", , , , True 'change test to your password
On Error Resume Next
Range("D9:E39").Value = ""
Range("G9:i39").Value = ""
Range("B44:B49").Value = ""
Range("D44:E49").Value = ""
Range("g44:I49").Value = ""
MsgBox ("Your entries have been cleared.")
End Sub

This works ok, know its not pretty but it will do the job for now.
Thanx everyone