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
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