Need Help Troubleshooting

[mastermind]

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 Before:=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

 

[Trevor Shuttleworth]

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

 

[FSt1]

hi,
just guessing.
coping an entire sheet eats up the clipboard. and doing it several time is
may be crashing the macro. I've had this happen to me in the past when i had
the copy/paste commands in a loop. so i learned to avoid this by useing
variables ie assign a range(or cell) to a variable and then have another
range(or cell) as a variable equal the value of the first range thereby avoid
the cllipboard altogeather.
try this. after the copy add...
Application.cutcopymode = false.
in your code i don't set any where else you use the copy commands.
if that don't work well enough then rethink your copy and only copy what you
need instead of the entire sheet. or try it with variables.

Regards
FSt1