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:

Please Login or Register  to view this content.

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:

Please Login or Register  to view this content.

(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

Please Login or Register  to view this content.

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