VBA Delete Sheets Based on Name Found in Array-CodePudding

Hi every month I get a report with historical worksheets in my workbook and I have to delete these worksheets before I do my analysis. My current macro deletes the sheets but I have to type out each name one by one. Is there an easier way to do this? Like have my FOR loop, loop through an array of names?

Set wb = ActiveWorkbook
For Each ws In wb.Worksheets
  If ws.Name = "Summary" Then
    ws.Visible = True
  ElseIf ws.Name = "Novemberdata" Then
    ws.Delete
  ElseIf ws.Name = "Novembersales" Then
    ws.Delete
  ElseIf ws.Name = "Decemberdata" Then
    ws.Delete
  ElseIf ws.Name = "DecemberSales" Then
    ws.Delete
  ElseIf ws.Name = "Januarydata" Then
    ws.Delete
  ElseIf ws.Name = "January Sales" Then
    ws.Delete
  Else: ws.Visible = xlSheetHidden
  End If
 Next

CodePudding user response：

If there's no pattern to the sheets that need to be deleted, you can just list them in a string, then split it to create an array.

Then iterate through the array items and try to delete each sheet. If you use On Error Resume Next, it will ignore errors encountered by each delete operation.

Something like this:

Option Explicit


Public Sub Setup()

Dim wb As Workbook

Set wb = ActiveWorkbook

Dim sheetsToDelete() As String
Dim sheetName As Variant

sheetsToDelete = Split("Novemberdata,Novembersales,Decemberdata,Decembersales,Januarydata,January Sales", ",")

For Each sheetName In sheetsToDelete

    On Error Resume Next
    wb.Worksheets(sheetName).Delete
    On Error GoTo 0

Next sheetName


End Sub