Run-time error 1004: Select method of range class failed

[kelly mort]

Sub clearsheets ()
   Dim Wsheet As Worksheet 
    For Each WSheet In Worksheets 
       Select Case Wsheet.CodeName
           Case "Sheet1", "Sheet2","Sheet3"
            Case Else 
           Wsheet.Range(Wsheet.Cells(7, 2),  Wsheet.Cells (200, 15)).Select 
Selection.ClearContents 
End Select 
Next 
End Sub

This is what I have been using for a while now. But just ran it and get the error at the title of this post.

I need help

 

[Norie]

Try this.

Sub clearsheets ()
Dim Wsheet As Worksheet 
    
    For Each WSheet In Worksheets 
        With WSheet
            Select Case .CodeName
                Case "Sheet1", "Sheet2","Sheet3"
                Case Else 
                    .Range(.Cells(7, 2),  .Cells (200, 15)).ClearContents 
             End Select 
         End With
    Next 

End Sub

 

[ClimoC]

What line does it bug on ? I'm assuming 'Select'.

 

[kelly mort]

Try this.

Sub clearsheets ()
Dim Wsheet As Worksheet 
    
    For Each WSheet In Worksheets 
        With WSheet
            Select Case .CodeName
                Case "Sheet1", "Sheet2","Sheet3"
                Case Else 
                    .Range(.Cells(7, 2),  .Cells (200, 15)).ClearContents 
             End Select 
         End With
    Next 

End Sub

Okay thanks. It has worked.

Thanks again.

 

[kelly mort]

What line does it bug on ? I'm assuming 'Select'.

Exactly