Selecting a cell

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

This is probably simple....
I have code to copy an existing worksheet, paste and (re)name it. I now
need to clear certain ranges on the added worksheet. The code works up to
the point where I have to select the first cell to clear and then I get the
following error:
****************************
*Run-time error '1004': *
*Select method of Range class failed *
****************************

Private Sub cmdAddScorecard_Click()
Dim NumSheets, x, y

y = Worksheets.Count
NumSheets = InputBox("How many sheets to add?")
If NumSheets = Empty Then
End
Else
For x = 1 To NumSheets
Sheets("1").Select
Sheets("1").Copy After:=Sheets(1)
y = y + 1
ActiveSheet.Name = y - 1
'Clear sheet contents
Range("B8").Select <-------------------I get the error
Selection.ClearContents
Range("B9").Select
Selection.ClearContents
Range("C14:C18").Select
Selection.ClearContents
Range("C25:C29").Select
Selection.ClearContents
Range("A42:E54").Select
Selection.ClearContents
Next x
End If
End Sub

If anyone has any idea of why I'm getting the error, your help is greatly
appreciated.
 
Ok - I found the answer. The error occurred because I was using relative
addressing instead of absolute addressing. I made the following change and
it worked:
Instead of .................. Range("B8").Select
I used ....................... ActiveSheet.Range("B8").Select
 
Back
Top