What version of Excel are you using ? How many is "a number of times" ?
What do the code names of the sheets look like ? Could they be something
like Sheet1111111... ?

You don't have to select things to work on them. So for example:

Range("F3,G6,G8,B17:K31,U17:AD31, ... ,C206:BF211").Select
Selection.ClearContents

would be:

Range("F3,G6,G8,B17:K31,U17:AD31, ... ,C206:BF211").ClearContents

Regards

Trevor


"mastermind" wrote in message
ups.com...
The code below is designed to copy the activesheet, clear the
contents, and place it at the front of the workbook. It works well,
but if I initiate the code a number of times without closing the
workbook I get an error:

Run-Time Error '1004'
Copy method of worksheet class failed.

Can any one figure out how to fix this problem, or explain why it
happens? Also if anyone has any suggestions for how to make my code
more efficient please let me know. Thank you.

John

Sub NewReport()
ActiveSheet.Copy Befo=Sheets(1)
ActiveSheet.Unprotect
Application.ScreenUpdating = False
Range("F3,G6,G8,B17:K31,U17:AD31,AE17:AX31,_
B35:AB54,AE34:BG54,B58:BG72,B88:BG149,I162:AD203,_
AK162:BE185,AK188:BD203,H205,C206:BF211").Select
Selection.ClearContents
Range("BK17:BK31").Value = 7

If Sheets(1).Range("AZ3").Value < "" And
IsNumeric(Sheets(1).Range("AZ3").Value) = True Then
ActiveSheet.Range("AZ3").Value = Sheets(2).Range("AZ3").Value
+ 1
Else
ActiveSheet.Range("AZ3").Value = Application.Sheets.Count - 1
End If

If Sheets(2).Range("AX6").Value < "" Then
ActiveSheet.Range("AX6").Value = Sheets(2).Range("AX6").Value
+ 1
Else
ActiveSheet.Range("AX6").Value = Date
End If

Rows("77:290").Select
Selection.EntireRow.Hidden = True
ActiveSheet.Shapes("Text Box 9").Visible = False
ActiveSheet.Shapes("Button 5").Select
Selection.Characters.Text = "Add Gridpaper"
ActiveSheet.Shapes("Button 6").Select
Selection.Characters.Text = "Add Time Log"
ActiveSheet.Shapes("Button 8").Select
Selection.Characters.Text = "Add Narrative"
Range("BK34:BK42").Value = 0

Range("B17").Select
ActiveSheet.Protect
Application.ScreenUpdating = True
End Sub