Delete All Worksheets in a Workbook without the names

[Dalcron]

Hi,

I'm not exactly a newbie to VBA or Visual Basic; however, I am having difficulty with making a part of a function delete all of the existing worksheets in a workbook despite their names.

General techniques that I have tried:

Application.DisplayAlerts = False

For Each sh In ThisWorkbook.Sheets
sh.Delete
Next

Application.DisplayAlerts = True

In the example above, I've replaced sh with other variables and I've also tried "In Worksheets" in place of "ThisWorkbook.Sheets"

When I try this, I get "Run-time error '1004': Delete method of Worksheet class failed"

The code I used to create the sheet inside the workbook is:

Dim sht As Worksheet
Set sht = Worksheets.Add

(I tried changing "sh" to "sht" too - fyi)

The function itself is quite lengthy so I left out the rest of the code... let me know if you need anything further to assist.

Thanks,
Dalcron

[Richard Schollar]

A workbook must have a minimum of one sheet so I suspect you get that error when the last sheet is being deleted.

[Marcol]

How far do you get before you get - "Run-time error '1004': Delete method of Worksheet class failed"

Excel will not let you delete all the sheets in a book, it always retains one visible sheet.

Why would you want to delete them all? Could you not just delete the workbook?

Or am I missing something?

[Dalcron]

I'm deleting them all in process of inputing a new one. However, I want my code to stay with the workbook and not just create a new one.

Thanks for this advice... I'll probably hide an unchanging worksheet that I need for later anyways, then I'll delete all but that one.

Thanks!
Greg

[Richard Schollar]

The remaining sheet does need to be visible so bear that in mind when running the code.

[Marcol]

From Excel warning

A workbook must contain at least one visible worksheet.
To, hide, delete, or move the selected sheet(s),you must first insert a new sheet or unhide a sheet that is already hidden.

Dalcron

I'm deleting them all in process of inputing a new one. However, I want my code to stay with the workbook and not just create a new one.

In that case why not delete all but one sheet

Sub DelSheets()
    Application.DisplayAlerts = False
    For i = Sheets.Count - 1 To 1 Step -1
        Sheets(i).Delete
    Next
    ActiveSheet.Cells.Clear
    Application.DisplayAlert = True
End Sub

Rename the remaining sheet to make that the first worksheet in your new set

You may have to add to the code to remove shapes controls charts etc from the last sheet