Selecting a group of worksheets to delete

[Guest]

I wish to get some code that will select and delete a group of sheets - those
that are between two other sheets "Frontsheet" and "Endsheet".

I have tried to record this but what I get is simply
Application.Goto Reference:="Frontsheet!R[1]C[1]"
ActiveSheet.Next.Select
Sheets(Array( "Sheetname1","Sheetname2"... "SheetnameZ").Select
etc

where "Sheetname1" is the sheet following "Frontsheet" and "SheetnameZ" is
that before "Endsheet"

The sheet names of the sheets between "Frontsheet" and "Endsheet" will not
always be the same and the number of sheet will also vary.

I am quite sure there is code out there to do this, but I'm slightly
stumped. can anyone give some guidance?

TIA
Ron

 

[Jan Karel Pieterse]

Hi Ron,

I wish to get some code that will select and delete a group of sheets - those
that are between two other sheets "Frontsheet" and "Endsheet".

Something like this:

Option Explicit

Sub DeleteBetween()
Dim oSh As Worksheet
Dim bStart As Boolean
Application.DisplayAlerts = False
For Each oSh In ActiveWorkbook.Worksheets
If oSh.Name = "Endsheet" Then Exit For
If oSh.Name = "Frontsheet" Then
bStart = True
ElseIf bStart Then
oSh.Delete
End If
Next
Application.DisplayAlerts = True
End Sub


Regards,

Jan Karel Pieterse
Excel MVP
www.jkp-ads.com

 

[Chip Pearson]

Try something like the following:

Dim Ndx As Long
Application.DisplayAlerts = False
For Ndx = Worksheets("FrontSheet").Index + 1 To _
Worksheets("EndSheet").Index - 1
Worksheets(Ndx).Delete
Next Ndx
Application.DisplayAlerts = True



--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting, LLC
www.cpearson.com


message
news:[email protected]...

 

[Bob Phillips]

Ron,

Here is some code to do it

Sub DeleteSheets()
Dim iStart As Long
Dim iEnd As Long
Dim i As Long

iStart = 2 'assume Frontsheet is sheet1
iEnd = ActiveWorkbook.Sheets.Count - 1 'assume Endsheet is last
Application.DisplayAlerts = False
For i = iEnd To iStart Step -1
ActiveWorkbook.Sheets(i).Delete
Next i
Application.DisplayAlerts = True

End Sub

--

HTH

RP
(remove nothere from the email address if mailing direct)

 

[Bob Phillips]

Ron,

A better version that doesn't assume that Frontsheet is sheet 1 and EndSheet
is the last one, but will work if they are

Sub DeleteSheets()
Dim iStart As Long
Dim iEnd As Long
Dim i As Long

iStart = ActiveWorkbook.Sheets("FrontSheet").Index + 1
iEnd = ActiveWorkbook.Sheets("EndSheet").Index - 1
Application.DisplayAlerts = False
For i = iEnd To iStart Step -1
ActiveWorkbook.Sheets(i).Delete
Next i
Application.DisplayAlerts = True

End Sub

--

HTH

RP
(remove nothere from the email address if mailing direct)