Auto delete worksheet after period of time

[traub86]

I need a way to have a worksheet auto-delete after a period of 3 years from when it was created. I currently have worksheets being exported from a working document every time I click an export macro button. This copies that worksheet with the date as the name of the tab. Not sure if this can be worked into the vba code or if its a setting within the workbook itself. Any help would be appreciated. Heres the code i have to export:

Sub Copysht()
    Workbooks("FINAL_JUMP MANIFEST_FINAL.xlsm").Sheets("DZ LOG").Copy before:=Workbooks("DO NOT DELETE_MASTER DZ LOG.xlsm").Sheets(1)
    Workbooks("DO NOT DELETE_MASTER DZ LOG.xlsm").Sheets(1).Name = Format(Date, "DD-MM-YYYY") + " Chalk 1"
End Sub

 

[JLGWhiz]

This might work. Add it before End Sub.

For Each sh In Workbooks("DO NOT DELETE MASTER DZ LOG.xlsm").Sheets
    If Len(sh.Name) >= 10 Then
        If Date - CDate(Left(sh.Name, 10)) >= 365 Then
            Application.DisplayAlerts = False
            sh.Delete
            Application.DisplayAlerts = True
        End If
    End If
Next

 

[traub86]

Ok and if I needed say a different time frame, say 1 day so I can verify if it works, how might I change it to reflect that?

 

[JLGWhiz]

Ok and if I needed say a different time frame, say 1 day so I can verify if it works, how might I change it to reflect that?

I used 365 in the Post #2 code, which is one year. Just change that figure to multiples of 365 or years, multiples of 30 for months or any number for days. Add 1 for any value divisible by 4 to accomodate leap years.
So for three years it would be:

If Date - CDate(Left(sh.Name, 10)) >= 1095 Then

It is crude, but it works. All the code does is subtract the date used in the sheet name from the current date which produces a value in days.

 

[traub86]

Haven't been on here in a while, but just tested the code and an error message popped up saying "type mismatch' with If Date - CDate(Left(sh.Name, 10)) >= 1095 Then highlighted...any suggestions?