Deleting all sheets except one

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Nirmal Singh - 26 Jan 2006 15:50 GMT

I have a workbook in which I need to delete all sheets except one
called "SummaryReport". I have tried using the following code

For z = 1 To xlBook.Worksheets.Count
If xlBook.Worksheets(z).Name <> "SummaryReport" Then
xlBook.Worksheets(z).Delete
Next z

This is falling over (I presume because the count changes as sheets
are deleted). How can I do this?

Nirmal

Reply to this Message

Ron de Bruin - 26 Jan 2006 15:57 GMT

You can use this

For Each sh In ThisWorkbook.Worksheets

If sh.Name <> "SummaryReport" Then

Signature

Regards Ron de Bruin
http://www.rondebruin.nl

>I have a workbook in which I need to delete all sheets except one
> called "SummaryReport". I have tried using the following code
[quoted text clipped - 8 lines]
>
> Nirmal

Reply to this Message

Nirmal Singh - 26 Jan 2006 16:01 GMT

>I have a workbook in which I need to delete all sheets except one
>called "SummaryReport". I have tried using the following code
[quoted text clipped - 8 lines]
>
>Nirmal

I have changed the loop

for z=xlBook.Worksheets.count to 1 step -1

It works now.

Nirmal

Reply to this Message