Thursday, October 17, 2019

Quickly Delete Many Excel Worksheets

In "Reverse the Order of Worksheets in an Excel Workbook" I show a VBA module that I wrote in order to reverse the order of several dozen worksheets in an Excel file.

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


No comments: