CODEWITHSUNDEEP
excelvba
Kunj Patel
Kunj Patel

Reputation: 75

Deleting Sheets from Excel file using VBA

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

Upvotes: 0

Views: 3353

Answers (2)

Mathieu Guindon
Mathieu Guindon

Reputation: 71167

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.

Upvotes: 2

Tim
Tim

Reputation: 48

dim i as Integer
dim ws as Worksheet

For i = 2 to ActiveWorkbook.Worksheets.Count
   Sheets(2).Delete
Next

Upvotes: 1

Related Questions