Jeff,

Worksheet_Deactivate is an event macro.
It runs anytime that worksheet is deactivated.
To prevent event macros from running, use...
Application.EnableEvents = False at the beginning of the Sub and
Application.EnableEvents = True before ending the Sub.
Also, make sure any error handling you use turns events back on.

Regards,
Jim Cone
San Francisco, USA


"Jeff Wright" wrote in
message news:w64Ie.55013$4o.51661@fed1read06...
Thanks, Paul, Jim, Jim & Bob for your responses. I now have the "sheet
delete" macro working. But now I have another problem with this. I've
inserted the full version of this macro into my main program, which is
referenced by a 'call.' When I F8 through the macro, it starts off deleting
sheets as it should, but right in the middle of the routine, it unexpectedly
jumps to a sheet macro (Private Sub Worksheet_Deactivate()). I can't delete
the sheet macro as it's a vital part of my program. So why does my "sheet
delete" macro seem to jump out into a totally unrelated macro??? I've had
this problem with other workbooks and have no idea how to rationalize this
phenomenon (or fix it!)

Below is the macro which deletes sheets, then unexpectedly jumps to another
totally unrelated macro. In this macro, I'm using a reverse index to delete
sheets (to solve the previous problem I posted), although I've found that
the sheets collection method works equally as well. Please let me know if
there's a way to solve this "jumping to another macro" problem.

Thanks again,

Jeff

(Note: The following macro runs after a macro in the main program opens up
and activates a blank workbook)

Sub PrepareSheet()

' This macro ensures that there are only seven worksheets
' in the workbook, and that all worksheets are named as Saveload1,
Saveload2, etc.

Application.ScreenUpdating = False
x = ThisWorkbook.Worksheets.Count
If x = 7 Then GoTo renayme ' seven sheets exist, now time to change sheet
names

'add sheets until seven exist
If x < 7 Then
y = 7 - x
For i = 1 To y
ThisWorkbook.Worksheets.Add
Next i

Else: ' delete excessive sheets until seven exist
Application.DisplayAlerts = False
y = x - 7
For i = y To 1 Step -1
ThisWorkbook.Worksheets(1).Delete ' THIS IS WHERE THE MACRO JUMPS
INTO ANOTHER UNRELATED MACRO
Next i
Application.DisplayAlerts = True
End If

renayme:
On Error Resume Next
Sheets(1).Activate
ActiveSheet.Name = "SaveLoad1"
Sheets(2).Activate
ActiveSheet.Name = "SaveLoad2"
Sheets(3).Activate
ActiveSheet.Name = "SaveLoad3"
Sheets(4).Activate
ActiveSheet.Name = "SaveLoad4"
Sheets(5).Activate
ActiveSheet.Name = "SaveLoad5"
Sheets(6).Activate
ActiveSheet.Name = "SaveLoad6"
Sheets(7).Activate
ActiveSheet.Name = "SaveLoad7"
On Error GoTo 0
Application.ScreenUpdating = True
End Sub