Why does this code not work across all designated sheets now ?

[Corey]

The below code suppose to clear the values in the Range to = ""
But for some reason it works if i run the macro from an activeworksheet,
but it does NOT Clear the values in ALL shets(Minus the named 3 sheets).

I also had to add the (On error resume next) as i was getting a Cannot change part of a Merged Cell
error,
But with those ranges there Are NO Merged Cells????

Sub ClearTimeSheetValues()
Call Unprotect
On Error Resume Next
Dim myrange As Range
For Each sh In ThisWorkbook.Worksheets
If sh.Name <> "Enter - Exit" And sh.Name <> "Utilization Sheet" And sh.Name <> "Leave Blank" Then
Set myrange = Range("C5:C16,F5:F16,I5:I16,L5:L16,O5:O16,R5:R16,U5:U15")
With myrange
..Value = ""
Range("A1").Select
End With
End If
Next
Call protect
End Sub

Any ideas' to get this code working again ?

Corey....

 

[Doug Glancy]

Corey,

Some of your range references are referring to the active worksheet (over
and over). You need to qualify the ranges you are referring to, for
example:

Set myrange = ws.Range("C5:C16,F5:F16,I5:I16,L5:L16,O5:O16,R5:R16,U5:U15")
and
ws.Range("A1").Select

I suspect that if you look at the active worksheet you'll find a merged cell
in one of the ranges referred to in your code, which would explain that
problem.

hth,

Doug

 

[NickHK]

Unqualified objects (normally) refer to the Activesheet. hence:
Range("C5:C16,F5:F16,I5:I16,L5:L16,O5:O16,R5:R16,U5:U15")
is always the same sheet. You need to include the reference to which sheet
the range is on. i.e.
sh.Range("C5:C16,F5:F16,I5:I16,L5:L16,O5:O16,R5:R16,U5:U15")

Also, what do you intend with this ?
Range("A1").Select

Do you mean ?
sh.Range("A1").Select

If so you will need to activate the sheet first, as you cannot .select on a
non-active sheet.

NickHK

 

[Corey]

Thanks Nick.

I have a litle better understanding of why it was not working now.
All is good.

Cheers

Unqualified objects (normally) refer to the Activesheet. hence:
Range("C5:C16,F5:F16,I5:I16,L5:L16,O5:O16,R5:R16,U5:U15")
is always the same sheet. You need to include the reference to which sheet
the range is on. i.e.
sh.Range("C5:C16,F5:F16,I5:I16,L5:L16,O5:O16,R5:R16,U5:U15")

Also, what do you intend with this ?
Range("A1").Select

Do you mean ?
sh.Range("A1").Select

If so you will need to activate the sheet first, as you cannot .select on a
non-active sheet.

NickHK