Each tab contains a summary of data for a week. After a few years, I had amassed over 100 worksheets. So I decided I would split the workbook; each one would contain only one year's worth of data. The workbook for 2018 would have only the 2018 worksheets; 2017 workbook, the 2017 worksheets; etc.
I copied the massive workbook to a 2018 workbook, from which I'd delete all but the 2018 worksheets. Ditto for 2017, 2016, and, oh yes, 2019, as well.
Unfortunately I found this to be exceedingly tedious. There didn't seem to be a way to delete multiple worksheets quickly and without many keystrokes and/or mouse clicks. At best, I was able to select the six tabs that could be displayed at one time by clicking the left-most tab and shift-clicking the right-most tab. Then I could right-click and delete the selected tabs. But I'd have to do that about 20 times for each workbook!
So instead, I wrote the following VBA module to do it effortlessly. Note that each worksheet is named with the date in YYYY-MM-DD format. (So the worksheet for today would be named 2019-10-17. ) This module was used to delete all the 2019 worksheets. Rather than write a nested loop to cycle through multiple years, I decided to change the year in the code manually.
Sub DeleteNewWorksheets()
' 2019-03-24 TG Created to clean up status records
Dim Sheet As Worksheet
Dim Book As Workbook
Dim n As String
Dim alerts As Boolean
Dim i As Integer
alerts = Application.DisplayAlerts
Application.DisplayAlerts = False
Set Book = ActiveWorkbook
i = 0
For Each Sheet In Book.Sheets
n = Left(Sheet.Name, 4)
If n = "2019" Then
Debug.Print "Deleted " & Sheet.Name
Sheet.Delete
i = i + 1
End If
Next
Application.DisplayAlerts = alerts
MsgBox "Deleted " & i & " sheets.", vbInformation, "DeleteOldWorksheets Notification"
End Sub
