PDA

View Full Version : Solved: Delete oldest sheet



JShelb
12-15-2008, 09:04 AM
Hi all,
I have a problem that has me slightly stumped. I have an excel workbook that has a main sheet that upon button click creates a worksheet for the current day and uses the date as the name. This sheet is used 6 days a week and the worksheets tend to build up rather quickly. Data only needs to be preserved for 1 month (31 days).

I currently have the following code that deletes the sheet that is 31 days older than the current date. I.E. sheet created on 12-15-08 deletes the sheet for 11-15-08. Well, since it is only used 6 days and not 7 days, I get single sheets dangling around after awhile.

Current code for what I have:

Sub DeleteSheets()
Dim nday, cday As String
cday = Cells.Range("m7")
nday = Format(CDate(cday) - 31, "mm-dd-yy")

On Error GoTo ErrorHandler
If Len(Sheets(nday).Name) > 0 Then
Application.DisplayAlerts = False
Sheets(nday).Delete
Application.DisplayAlerts = True
End If

ErrorHandler:
Select Case Err.Number
Case 9
Resume Next
End Select
End Sub


What I would like to do is change the way I delete the sheets by using something along the lines of:

If Sheets.Count > 31 Then
OldestSheet.Delete
End If


So if sheet count was 32, it would delete the 32nd sheet.

Thank you very much in advance and if more info is needed just let me know.

-Jason

Bob Phillips
12-15-2008, 09:23 AM
Sub DeleteSheets()
Dim sh As Worksheet, cday As String
cday = Cells.Range("m7")

On Error GoTo ErrorHandler

Application.DisplayAlerts = False
For Each sh In ActiveWorkbook.Worksheets

If IsDate(DateValue(sh.Name)) Then
If DateValue(sh.Name) < cday Then
sh.Delete
End If
End If
Nextsh:
Next sh

Application.DisplayAlerts = True
Exit Sub

ErrorHandler:
Select Case Err.Number
Case 13
Resume Nextsh
End Select
End Sub

JShelb
12-15-2008, 10:15 AM
I had to add the nday variable back in. The code you posted worked fine, however, it deleted any worksheet older than the current date. I needed it to delete any worksheet 31 days or older than the current date. Just a minor tweak to get it going right. Thank you for your help in getting this resolved, much appreciated.


Sub DeleteSheets()
Dim sh As Worksheet, cday As String, nday As String
cday = Cells.Range("m7")
nday = CDate(cday) - 31

On Error GoTo ErrorHandler

Application.DisplayAlerts = False
For Each sh In ActiveWorkbook.Worksheets
If IsDate(DateValue(sh.Name)) Then
If DateValue(sh.Name) < DateValue(nday) Then
sh.Delete
End If
End If
Nextsh:
Next sh

Application.DisplayAlerts = True
Exit Sub
ErrorHandler:
Select Case Err.Number
Case 9
Resume Next
Case 13
Resume Nextsh
End Select
End Sub

Bob Phillips
12-15-2008, 11:06 AM
I got that, and mistakenly thought it was I had the wrong value in M7.

You can do it without nday



Sub DeleteSheets()
Dim sh As Worksheet, cday As String
cday = Cells.Range("m7") - 31

On Error Goto ErrorHandler

Application.DisplayAlerts = False
For Each sh In ActiveWorkbook.Worksheets

If IsDate(DateValue(sh.Name)) Then
If DateValue(sh.Name) < cday Then
sh.Delete
End If
End If
Nextsh:
Next sh

Application.DisplayAlerts = True
Exit Sub

ErrorHandler:
Select Case Err.Number
Case 13
Resume Nextsh
End Select
End Sub