Deleting all sheets except one

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

On Thu, 26 Jan 2006 15:54:14 +0000, Nirmal Singh
<nirmal.singh@nottowerhamlets.notgov.uk> wrote:

>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

I have changed the loop

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

It works now.

Nirmal

You can use this

For Each sh In ThisWorkbook.Worksheets

If sh.Name <> "SummaryReport" Then


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


"Nirmal Singh" <nirmal.singh@nottowerhamlets.notgov.uk> wrote in message news:lmrht1dqvtkoutd2bq0isf97c9ik58btdv@4ax.com...
>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