Good evening all,



In the following macro, I'm attempting to delete 20 worksheets from a
workbook that has, say 30 worksheets. When I run it, it deletes several
sheets, but then it crashes with a runtime error 9 (subscript out of range).
I know that an array has to be DIM'd, but I can't figure out the name of the
name of the array to be DIM'd. Is the array going to something like
"worksheets(50)"?? I've tried this and it doesn't work. Please help!



Thanks,



JW





Sub test()

Application.DisplayAlerts = False

For i = 1 To 20

ThisWorkbook.worksheets(i).Delete

Next i

Application.DisplayAlerts = True

End If

Your problem is the way you are referring to the worksheets to be deleted.

Try using the worksheets collection rather than individual worksheets.

Something like


Sub test()
dim wks as worksheet
dim n as integer
Application.DisplayAlerts = False

for each wks in activeworkbook.worksheets
for n = 1 to 20
wks.delete
next n

Application.DisplayAlerts = true
next wks


"Jeff Wright" wrote in message
news:waUHe.54739$4o.35775@fed1read06...
Good evening all,



In the following macro, I'm attempting to delete 20 worksheets from a
workbook that has, say 30 worksheets. When I run it, it deletes several
sheets, but then it crashes with a runtime error 9 (subscript out of
range).
I know that an array has to be DIM'd, but I can't figure out the name of
the
name of the array to be DIM'd. Is the array going to something like
"worksheets(50)"?? I've tried this and it doesn't work. Please help!



Thanks,



JW





Sub test()

Application.DisplayAlerts = False

For i = 1 To 20

ThisWorkbook.worksheets(i).Delete

Next i

Application.DisplayAlerts = True

End If

Have you tried this code... It crashes for me... For each worksheet in the
workbook you are delete that sheet 20 times? Looks like an error to me...
Maybe something more like (untested)

dim wks as worksheet
dim n ans integer

n = 1
for each wks in worksheets
wks.delete
if n = 20 then exit sub
n = n+1
next wks
--
HTH...

Jim Thomlinson


"Paul Smith" wrote:

Your problem is the way you are referring to the worksheets to be deleted.

Try using the worksheets collection rather than individual worksheets.

Something like


Sub test()
dim wks as worksheet
dim n as integer
Application.DisplayAlerts = False

for each wks in activeworkbook.worksheets
for n = 1 to 20
wks.delete
next n

Application.DisplayAlerts = true
next wks


"Jeff Wright" wrote in message
news:waUHe.54739$4o.35775@fed1read06...
Good evening all,



In the following macro, I'm attempting to delete 20 worksheets from a
workbook that has, say 30 worksheets. When I run it, it deletes several
sheets, but then it crashes with a runtime error 9 (subscript out of
range).
I know that an array has to be DIM'd, but I can't figure out the name of
the
name of the array to be DIM'd. Is the array going to something like
"worksheets(50)"?? I've tried this and it doesn't work. Please help!



Thanks,



JW





Sub test()

Application.DisplayAlerts = False

For i = 1 To 20

ThisWorkbook.worksheets(i).Delete

Next i

Application.DisplayAlerts = True

End If

There is a problem doing this because of the delete... When you delete the
index shifts on you and when you increment your counter you are skipping over
sheets... The code is generally ok but try this...

Sub test()

Application.DisplayAlerts = False

For i = 1 To 20

ThisWorkbook.worksheets(1).Delete 'Change i to 1

Next i

Application.DisplayAlerts = True

End If

--
HTH...

Jim Thomlinson


"Jeff Wright" wrote:

Good evening all,



In the following macro, I'm attempting to delete 20 worksheets from a
workbook that has, say 30 worksheets. When I run it, it deletes several
sheets, but then it crashes with a runtime error 9 (subscript out of range).
I know that an array has to be DIM'd, but I can't figure out the name of the
name of the array to be DIM'd. Is the array going to something like
"worksheets(50)"?? I've tried this and it doesn't work. Please help!



Thanks,



JW





Sub test()

Application.DisplayAlerts = False

For i = 1 To 20

ThisWorkbook.worksheets(i).Delete

Next i

Application.DisplayAlerts = True

End If

Jeff,

After you have deleted a majority of the sheets then
the index number is larger than the number of sheet
left in the workbook.
Run you loop in reverse...

For i = 20 To 1 Step -1

Jim Cone
San Francisco, USA



"Jeff Wright" wrote in message
news:waUHe.54739$4o.35775@fed1read06...
Good evening all,
In the following macro, I'm attempting to delete 20 worksheets from a
workbook that has, say 30 worksheets. When I run it, it deletes several
sheets, but then it crashes with a runtime error 9 (subscript out of range).
I know that an array has to be DIM'd, but I can't figure out the name of the
name of the array to be DIM'd. Is the array going to something like
"worksheets(50)"?? I've tried this and it doesn't work. Please help!
Thanks,
JW

Sub test()
Application.DisplayAlerts = False

For i = 1 To 20
ThisWorkbook.worksheets(i).Delete
Next i
Application.DisplayAlerts = True
End If '<<<<<???

When deleting, work backwards, or bottom up, to ensure that you are not
altering the data that you have yet to process.

--
HTH

Bob Phillips

"Jeff Wright" wrote in message
news:waUHe.54739$4o.35775@fed1read06...
Good evening all,



In the following macro, I'm attempting to delete 20 worksheets from a
workbook that has, say 30 worksheets. When I run it, it deletes several
sheets, but then it crashes with a runtime error 9 (subscript out of
range).
I know that an array has to be DIM'd, but I can't figure out the name of
the
name of the array to be DIM'd. Is the array going to something like
"worksheets(50)"?? I've tried this and it doesn't work. Please help!



Thanks,



JW





Sub test()

Application.DisplayAlerts = False

For i = 1 To 20

ThisWorkbook.worksheets(i).Delete

Next i

Application.DisplayAlerts = True

End If

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

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

Jim, thanks so much for your help on this!!!

Jeff in Tucson



"Jim Cone" wrote in message
...
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