How to delete sheets older than this month-CodePudding

I have a workbook with various sheets (sheet names are dates with this format DD.MM.YYYY)

I am using the following macro to create a new sheet, delete whatever is on a given range, and give todays date on a new sheet:

ActiveSheet.Copy Before:=Sheets(1)
Range("B5:I" & Range("B4").End(xlDown).Row).Select
Selection.ClearContents
ActiveSheet.Name = Format(Date, "DD.MM.YYYY")

I also want to create a new macro in order to delete sheets from previous months (everything except this month). I have tried the given solutions on this thread How to delete sheet older than a month? but nothing is even working.

Not very experienced with vba so any help is welcome. Working on excel 2019.

CodePudding user response：

Please, try the next way:

Sub deleteSheetsByMonth()
  Dim sh As Worksheet
  
  For Each sh In ActiveWorkbook.Worksheets
       If IsDate(sh.Name) Then
          If DateDiff("m", CDate(sh.Name), Date) > 0 Then sh.Delete
       End If
  Next sh
End Sub

Are there such hidden sheets?

CodePudding user response：

Option Explicit

Sub deleteSheetsByMonth()
    Dim ws As Worksheet, a, dtWs As Date, dt1 As Date
    Dim msg As String
    
    dt1 = DateSerial(Year(Date), Month(Date), 1) ' 1st of month
    For Each ws In ActiveWorkbook.Worksheets
        If ws.Name Like "##.##.####" Then
            a = Split(ws.Name, ".")
            dtWs = DateSerial(a(2), a(1), a(0))
            If dtWs < dt1 Then
                msg = msg & vbLf & ws.Name
                Application.DisplayAlerts = False
                ws.Delete
                Application.DisplayAlerts = False
            End If
        End If
    Next
    If msg <> "" Then
        MsgBox "Sheets deleted:" & msg, vbInformation
    Else
        MsgBox "No Sheets deleted", vbInformation
    End If
End Sub