Deleting Sheets from Excel file using VBA

Question

I am trying to delete all unnecessary sheets from an activeWorkbook, but Sheet1 because it will be my primary sheet with initial raw data in it. When I run the following code, my sheets gets deleted, but I get this error once it reaches my last remaining sheet. Run-time error '1004': A workbook must contain at least one visible worksheet. FYI: I am writing this macro in personal macro book so i can use it for all other excel spreadsheets.

Sub deleteSheets()

Dim ws As Worksheet

   Application.DisplayAlerts = False

   For Each ws In ActiveWorkbook.Worksheets
      If Not ws Is Sheet1 Then ws.Delete
   Next ws

End Sub

There isn't always a "Sheet1" in every workbook - are you trying to keep the first sheet in the collection? — dwirony
@dwirony yes, the first sheet in the collection. Usually it will be Sheet1 since it's the default, correct? — Kunj Patel
Not always - if you make a new workbook, add a new sheet and delete the first one, then you'll only have a Sheet2 in your workbook. — dwirony
@dwirony Ahh- understood! so how should i go about fixing this error? assuming the name of my first sheet is "Sheet1" or the first sheet in collection? — Kunj Patel

Mathieu Guindon Mathieu Guindon · Accepted Answer · 2019-03-14T19:42:00

If Not ws Is Sheet1 Then ws.Delete

The Boolean expression {object1} Is {object2} evaluates reference equality: it will be True if {object1} is {object2}.

Here ws is a worksheet in ActiveWorkbook, and Sheet1 is (presumably) a global identifier referring to the CodeName of a sheet that exists in ThisWorkbook - which may or may not be the currently active workbook.

If ThisWorkbook isn't the ActiveWorkbook, reference equality will fail for every single sheet, and that loop will attempt to delete them all, which is why you're getting that error.

Unless you want to delete sheets in ThisWorkbook, you can't use reference equality to make that check.

You can go by Index, or by name - in any case it's a good idea to ensure there's more than a single sheet in the workbook you're working with:

For Each ws In ActiveWorkbook.Worksheets
    Debug.Assert ActiveWorkbook.Worksheets.Count > 1
    If Not ws.Name = "Sheet1" Then ws.Delete
Next

Verify that the sheet you mean to delete has the expected index (1) and/or the expected name; use the Project Explorer (Ctrl+R) and the Properties (F4) toolwindows for that. Bear in mind that the (Name) property is the sheet's CodeName identifier, which you can only use/access in the VBA project of ThisWorkbook; you'll want to verify its Name property - that's the user-modifiable "tab name" of the worksheet.

Deleting Sheets from Excel file using VBA

2 Answers