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

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" wrote in message ...
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
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