Delete all sheets after sheet1, but not by sheet name
 

is there some code where i can delete all sheets, except for sheet1, but not
by the sheet name.
As the sheet names are not in any particular sequence or indexed value?
Corey....

Delete all sheets after sheet1, but not by sheet name
 

Hi Corey,

To delate all sheers except for the first sheet, try:

'=============
Public Sub Tester001()
Dim WB As Workbook
Dim SH As Object
Dim i As Long

Set WB = Workbooks("YourBook.xls")

With Application
.ScreenUpdating = False
.DisplayAlerts = False
End With

For i = WB.Sheets.Count To 2 Step -1
WB.Sheets(i).Delete
Next i

With Application
.ScreenUpdating = True
.DisplayAlerts = True
End With
End Sub
'<<=============

To deleta all sheets except for a sheet named Sheet1, try instead:

'=============
Public Sub Tester002()
Dim WB As Workbook
Dim SH As Object
Const ShName As String = "Sheet1" '<<==== CHANGE

Set WB = Workbooks("YourBook.xls")

With Application
.ScreenUpdating = False
.DisplayAlerts = False
End With

For Each SH In WB.Sheets
If SH.Name < ShName Then
SH.Delete
End If
Next SH

With Application
.ScreenUpdating = True
.DisplayAlerts = True
End With
End Sub
'<<=============


---
Regards,
Norman



"Corey" wrote in message
...

is there some code where i can delete all sheets, except for sheet1, but
not by the sheet name.
As the sheet names are not in any particular sequence or indexed value?
Corey....

Delete all sheets after sheet1, but not by sheet name
 

How bout something like:

Sub DeleteAllButFirst()
Dim i As Integer
Application.DisplayAlerts = False
For Each Sh In Worksheets
If Sh.Index 1 Then
Sh.Delete
End If
Next
Application.DisplayAlerts = True
End Sub

Regards,
Jim May


"Corey" wrote in message
:

is there some code where i can delete all sheets, except for sheet1, but not
by the sheet name.
As the sheet names are not in any particular sequence or indexed value?
Corey....

Delete all sheets after sheet1, but not by sheet name
 

Thanks Norman and Jim,
Too easy POST viewing the code isn't it.

Thanks Guys.

Corey....

Delete all sheets after sheet1, but not by sheet name
 

Sub DeleteAllButFirst()
Dim i as Long
Application.DisplayAlerts = False
for i = 1 to Sheets.count - 1
Sheets(2).Delete
Next
Application.DisplayAlerts = True
End Sub

--
Regards,
Tom Ogilvy

"Corey" wrote in message
...

is there some code where i can delete all sheets, except for sheet1, but
not by the sheet name.
As the sheet names are not in any particular sequence or indexed value?
Corey....

Delete all sheets after sheet1, but not by sheet name
 

Corey,

Dim i as Integer

For i = Sheets.Count to 1 Step -1 ' Step backwards since deleting will mess
up the total sheets
If Sheets(i).Name < "Sheet1" Then
Application.DisplayAlerts = False
Sheets(i).Delete
Applicaiton.DisplayAlerts = True
End If
Next i

Leave out the two lines for displaying alerts if you want the messagebox to
confirm the deletion.

David

"Corey" wrote in message
...

is there some code where i can delete all sheets, except for sheet1, but
not by the sheet name.
As the sheet names are not in any particular sequence or indexed value?
Corey....