Excel 2016 Delete multiple tabs that change names as the year changes

Question

I have a workbook that is used every year that changes every year for example one year the worksheets are Jan 1, Jan 15, Jan 29, Feb 12 and so on, next year it has worksheets that are Jan 2, Jan 16, Jan 30, Feb 13 and so on. Well every year at the end of every year I need to delete those worksheets and then replicate the new worksheets from the template worksheet. So as you can tell the names of the worksheets change every year and I would like to delete the worksheets all at once and not leave room for errors. So I recorded a vba script but I cannot find a way to just delete all the worksheets that are Jan*, Feb*, Mar*, Apr* and so on. Below is an example of what I have but does not work.

Sub Macro1()
'
' Macro1 Macro
'
    Sheets(Array("Jan 16", "Jan 30", "Feb 13", "Feb 27", "Mar 12", "Mar 26", "Apr 9", _
        "Apr 23", "May 7", "May 21", "Jun 4", "Jun 18", "Jul 2", "Jul 16", "Jul 30", "Aug 13", _
        "Aug 27", "Sep 10", "Sep 24", "Oct 8", "Oct 22", "Nov 5", "Nov 19", "Dec 3", "Dec 31")) _
        .Select
    Sheets("Dec 31").Activate
    Sheets("Dec 17").Select Replace:=False
    ActiveWindow.SelectedSheets.Delete
End Sub

Answer 1

The code below will loop through all Sheets, and check is the Sheet.Name first 3 letters equals to one of the months inside MonthsArr array. The compare is done without a loop, using the Match function.

Note: I am keeping the "Dec 31" sheet, otherwise deleting all the sheets will raise a run-time error. If there are other sheets, the part with If sht.Name <> "Dec 31" Then can be removed.

Code

Option Explicit

Sub DelAllSheets()

Dim sht As Worksheet
Dim MonthsArr As Variant

MonthsArr = Array("Jan", "Feb", "Mar", "Apr", "May", "Jun", "Jul", "Aug", "Sep", "Oct", "Nov", "Dec")

Application.DisplayAlerts = False
For Each sht In Worksheets

    ' use Match function to see if first 3 letters are inside the Month Array
    If Not IsError(Application.Match(Left(sht.Name, 3), MonthsArr, 0)) Then
        ' don't delete the last date sheet, deleting all sheets in the workbook will raise a run-time error
        If sht.Name <> "Dec 31" Then                
            sht.Delete
        End If
    End If

Next sht
Application.DisplayAlerts = True

End Sub

Answer 2

Here's a go in one loop and one delete statement (which speeds up code):

Sub DeleteSheets()

Dim sMonList As String
sMonList = "JanFebMarAprMayJunJulAugSepOctNovDec"

Dim ws As Worksheet
For Each ws In ThisWorkbook.Worksheets
    Dim sNames As String
    If InStr(sMonList, Left(ws.Name, 3)) Then sNames = sNames & "," & ws.Name
Next

Dim vSheets As Variant
vSheets = Split(Mid(sNames, 2), ",")

Application.DisplayAlerts = False
Worksheets(vSheets).Delete
Application.DisplayAlerts = True

End Sub

N.B. - This could potentially fail if you have another sheet name that has the left 3 letters in the sMonList, but I am sure you can adjust if needed.

Answer 3

This will delete all the sheets based on the first three characters of the name of the sheet.

Sub deleteshts()
Dim sht As Worksheet
Dim monArr(), mon

monArr = Array("Jan", "Feb", "Mar", "Apr", "May", "Jun", "Jul", "Aug", "Sep", "Oct", "Nov", "Dec")

For Each sht In ThisWorkbook.Worksheets
    For Each mon In monArr
        If Left(sht.name, 3) = mon Then
            Application.DisplayAlerts = False
            sht.Delete
            Application.DisplayAlerts = True
            Exit For
        End If
    Next mon
Next sht
End Sub