Delete all sheets between Sheets X and Y

[JonoFish]

Hello
I have two divider sheets - one named "First_Sheet" and one named "Last_Sheet". In between these sheets are a number of different sheets based on a template (the number changes over time depending on how many "timesheets" have been submitted).

At the start of a VBA macro i simply want to make sure i delete all the existing sheets that are currently between those two sheets. (before the macro adds in new ones)

I thought it would be simple enough but its perplexing me.

Any assistance would be greatly appreciated.
VBA

 

[Michael M]

Hi and welcome to the Forum
Try

Sub MM1()
Dim ws As Worksheet
 Application.DisplayAlerts = False
    For Each ws In Worksheets
        If ws.Name <> "Last_Sheet" And ws.Name <> "First_Sheet" Then

            ws.Delete
        End If
    Next ws
Application.DisplayAlerts = True
End Sub

 

[Rick Rothstein]

At the start of a VBA macro i simply want to make sure i delete all the existing sheets that are currently between those two sheets.

The following code should do this...

Sub DeleteSheetsBetweenFirstAndLastMarkerSheets()
  Dim X As Long
  Application.DisplayAlerts = False
  For X = Sheets("Last_Sheet").Index - 1 To Sheets("First_Sheet").Index + 1 Step -1
    Sheets(X).Delete
  Next
  Application.DisplayAlerts = True
End Sub

 

[JonoFish]

Righto thanks RIck - i knew there must be a property to define the sheet order but didnt know what it was - seems worksheets.index is it.

Michael M - THanks for the suggestion - i thikn Rick's code will work better as there may be additional sheets added over time outside the first/last sheet range which i dont want to delete.
Cheers,
J