enter code hereA worksheet will automatically be created. The worksheet will have a date that is entered 2 days from the current date. I am having trouble getting it to delete that worksheet when it becomes 3 days old. Please see my code below for what I currently have.

For Each ws In Worksheets

    If ws.Name = Format(Date - 2, "MM-DD-YYYY") Then

        Application.DisplayAlerts = False

        Sheets(Date - 2, "MM-DD-YYYY").Delete

        Application.DisplayAlerts = True

If i understood your question you can try this code:

Sub test()

For Each ws In Worksheets

    If ws.Name = Format(Date - 2, "MM-DD-YYYY") Then

        Application.DisplayAlerts = False

        'control the date and delete the sheet 

        If (ws.Name = Format(Date - 2, "MM-DD-YYYY")) Then

            ws.Delete

            Application.DisplayAlerts = True

        End If

    End If

Next ws

End Sub

ex. if today is 01-02-2019 the macro delete the sheet with the date 12-31-2018 (MM-DD-YYYY). If you want delete the sheet 3 days old then you have to change value 2 in 3(within macro).

Hope this helps!

You aren't returning a formatted string into the Sheets collection.

For Each ws In Worksheets

    If ws.Name = Format(Date - 2, "MM-DD-YYYY") Then

        Application.DisplayAlerts = False

        Sheets(Format(Date - 2, "MM-DD-YYYY")).Delete   '<~~ identified here

        'alternate

        'ws.delete

        Application.DisplayAlerts = True

        exit for    '<~~ no point in continuing since only one worksheet could have that name

There is actually no point in building the worksheet name twice. If it passes the first name test then ws is the worksheet you want to delete.

I started thinking about weekends and holidays that might interfere with the '3 days old' rule. This will delete any worksheet that has a name as a string date three working days old or older.

For Each ws In Worksheets

    If cdate(ws.Name) <= APPLICATION.WORKDAY(Date, -3) Then

        Application.DisplayAlerts = False

        ws.delete

        Application.DisplayAlerts = True

    end if

NEXT WS

Delete Old Sheets

• This code includes the deletion of possible chart sheets.


• To enable deletion without confirmation you have to set
  cBlnWithoutConfirmation to True.

The Code

'*******************************************************************************

' Purpose:    Deletes all sheets named by a date in format MM-DD-YYYY

'             that are at least old as specified.

'*******************************************************************************

Sub DeleteOldSheets()



    Const cIntAge As Integer = 3            ' Age of Sheet (Days)

    Const cBlnWithoutConfirmation = False   ' Enable Delete Without Confirmation



    Dim vntDate As Variant                  ' Date Array

    Dim vntSheetDate As Date                ' Sheet Date

    Dim i As Integer                        ' Sheets Counter



    With ThisWorkbook

        For i = 1 To .Sheets.Count

            With .Sheets(i)

                vntDate = Split(.Name, "-")

                On Error Resume Next

                vntSheetDate = DateSerial(vntDate(2), vntDate(0), vntDate(1))

                If Err Then

                    On Error GoTo 0

                  Else

                    If Date - vntSheetDate + 1 > cIntAge Then

                        If cBlnWithoutConfirmation Then

                            Application.DisplayAlerts = False

                            .Delete

                            Application.DisplayAlerts = True

                          Else

                            .Delete

                        End If

                    End If

                End If

            End With

        Next

    End With



End Sub

'*******************************************************************************