[y0rk1e72]

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://www.excelguru.ca/forums/showt...list-of-sheets
http://www.mrexcel.com/forum/excel-q...st-sheets.html
http://www.excelforum.com/excel-prog...html?p=2998352

[isabelle]

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

[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

[y0rk1e72]

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

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.

[y0rk1e72]

Originally Posted by y0rk1e72

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

Formula:

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 


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