I have used a macro for months with no problems.
Yesterday, the macro started erroring out on the specified
line below. The code below is only part of the macro.

I believe the reason it is erroring out is this sheet does
not exist when it hits this line of code. I have looked
at the workbook and the sheet is missing. There is
nothing in the previous code to delete the sheet.

There are currently 3 sheets in the workbook. The 3rd
sheets is temporary and the line below is deleting it
because it is no longer needed.

When I step through the program, I do not get the debug
error on this line. When I run it as normal. I get the
error. Any suggestions to prevent this error would be
greatly appreciated.


Sub ReStart2()

Range("A1").Select

With Application
.Calculation = xlAutomatic
.MaxChange = 0.001
End With

ActiveWorkbook.PrecisionAsDisplayed = False

Application.ScreenUpdating = False

Range(Selection, Selection.End(xlDown)).Select
Application.CutCopyMode = False
Selection.Copy
Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone,
SkipBlanks:= False, Transpose:=False

Application.DisplayAlerts = False
Worksheets(3).Delete ' errors out here
Application.DisplayAlerts = True

End Sub

Well your code runs fine for me if there are at least 3 worksheets in the
active workbook. If there aren't I get a subscript error. If you
anticipate running this code when there are fewer than 3 sheets you could
put an On Error Resume Next before the delete to blow by any error...

Also you should consider putting the Application.CutCopyMode = False after
the paste, not before it (so the macro doesn't end in Copy mode).

--
Jim Rech
Excel MVP

Well if you always know worksheet 3 is going to be a temp on then you
can use

On Error Resume Next
Worksheets(3).Delete ' errors out here
On Error GoTo 0

that will take care of it.

I would recomend using a name on the worksheet to reference it to
delete it, I have had excel do some funny things when it comes to the
numeric index of worksheets.

Keith
www.kjtfs.com


---
Message posted from http://www.ExcelForum.com/