Deleting all sheets except one

[Nirmal Singh]

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

 

[Ron de Bruin]

You can use this

For Each sh In ThisWorkbook.Worksheets

If sh.Name <> "SummaryReport" Then

 

[Nirmal Singh]

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