ExcelBanter

ExcelBanter (https://www.excelbanter.com/)
-   Excel Programming (https://www.excelbanter.com/excel-programming/)
-   -   macro too fast??? (https://www.excelbanter.com/excel-programming/289570-macro-too-fast.html)

mike

macro too fast???
 
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

KJTFS[_75_]

macro too fast???
 
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/


Jim Rech

macro too fast???
 
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




All times are GMT +1. The time now is 09:09 PM.

Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
ExcelBanter.com