Can you Delete Worksheets based on oldest and count ? 2

[ljsmith91]

I have a macro that we execute periodically and creates a new worksheet in a workbook with a date for each sheet name that is the date the macro last ran. The problem is that over time we end up retaining too many worksheets. We only need the last 5 or so. How can I include in the macro a process for pruning the oldest worksheets and keeping only the last 5 ? Is this possible ?

Any help or direction would be great.

 

[TonyJollans]

Hi ljsmith91,

This will create a new sheet with the date as a name and then run through all the other sheets looking for the one whose name is the earliest date in the same format - you can probably adapt it to your own needs

SheetName = Date
SheetNameFormat = "dd MMM yyyy"
With ActiveWorkbook
    .Worksheets.Add.Name = Format(SheetName, SheetNameFormat)
    For Each Sheet In .Sheets
        If DateValue(Sheet.Name) < SheetName Then
            SheetName = DateValue(Sheet.Name)
        End If
    Next
    Application.DisplayAlerts = False
    Sheets(Format(SheetName, SheetNameFormat)).Delete
    Application.DisplayAlerts = True
End With

Enjoy,
Tony

--------------------------------------------------------------------------------------------
We want to help you; help us to do it by reading this: Before you ask a question.
Excel VBA Training and more Help at VBAExpress[

 

[ljsmith91]

TonyJollans,

Thanks so much. That is helpful in determining how to remove those sheets older than a specific date. I can use that. I still need to be able to prune based on how many sheets there are also. I only want to keep 5 sheets regardless of their dates. Is this possible ?

Thanks.

 

[Molby]

You can use:

Do Until ActiveWorkbook.Sheets.Count = 5
Loop

In conjunction with Tony's code