Delete range on open
 

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

Delete range on open
 

You want the left function.

Dim WS As Worksheet
For Each WS In Worksheets
If left(WS.Name,2) = "ZZ" Then
Range("j29:q38").ClearContents
End If
Next

Delete range on open
 

Sorry, I misunderstood slightly. Try this:

Dim WS As Worksheet
For Each WS In Worksheets
If not left(WS.Name,2) = "ZZ" Then
Range("j29:q38").ClearContents
End If
Next

Delete range on open
 

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

Delete range on open
 

Thanks Brian for you quick response, but
I don't understand why but it deleted the range in the worksheets that begin
with "ZZ". Here's what it looks like
Private Sub workbook_open()
'clear A9s
Dim WS As Worksheet
For Each WS In Worksheets
If Not Left(WS.Name, 2) = "ZZ" Then
Range("j29:q38").ClearContents
End If
Next

"Brian Taylor" wrote:

Sorry, I misunderstood slightly. Try this:

Dim WS As Worksheet
For Each WS In Worksheets
If not left(WS.Name,2) = "ZZ" Then
Range("j29:q38").ClearContents
End If
Next

Delete range on open
 

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

Delete range on open
 

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

Delete range on open
 

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