It was my pleasure...

hth, Rick



"cluck" wrote in message
...
Sorry, I got it to work. I had the range as a2:a300 when it should have
been
a3:a300.
Thanks so much for you help

"cluck" wrote:

Hello Rick and thanks
Butl I get a "subscipt out of range" on this line
Set ws = Worksheets(ckCell.Value)
I changed the "sheet1" to "ZZZListing" where the sheet list name is
located.


"Rick Hansen" wrote:

Good Morning, here is bit of code that will propbably get you pointed
in the
right direction. If you have any questions, posted me back...

enjoy, Rick (Fairbanks, AK {Land of the Midnight Sun})

===============================

Option Explicit

Sub ClearZone()
Dim ws As Worksheet
Dim trgRng As Range, ckCell As Range

'' Assume ZZ list on Sheet1
Set trgRng = Worksheets("Sheet1").Range("A2:A300")

For Each ckCell In trgRng
If Not Left(ckCell.Value, 2) = "ZZ" Then
If Not IsEmpty(ckCell.Value) Then
Set ws = Worksheets(ckCell.Value)
ws.Range("j29:q38").ClearContents
Else
Exit Sub '' found empty cell
End If
End If
Next ckCell

End Sub

=================================

"cluck" wrote in message
...
Hello, I've sure appreciated the help that I've gotten and found on
this
sight.
However I have a small problem.
I am wanting to Clear a range "j29:q38" on several sheets when a
workbook
opens. The sheet names vary and can increase or decrease in number.
I do
have
a range "ZZListing a2:a300" that contains employee names which
correspond
to
the sheet names that I I want to clear. But I have six other
sheets, that
I
don't want to clear, the names of these sheets all begin with ZZ, ie
ZZListing, ZZProjects, etc. I hope that I am explaining myself.
Here is the last code that I've tried.

Dim WS As Worksheet
For Each WS In Worksheets
If WS.Name < "ZZ" Then
Range("j29:q38").ClearContents

End If
Next