Can you tell me how to write code to delete all worksheets other tha
those with a pre-specified name. eg all sheets other than Sheet1

Thank

-----------------------------------------------
~~ Message posted from http://www.ExcelTip.com
~~View and post usenet messages directly from http://www.ExcelForum.com

Hello Andym,

Sub test()
Application.DisplayAlerts = False
For Each ws In ThisWorkbook.Sheets
If ws.Name < "Sheet1" Then ws.Delete
Next ws
Application.DisplayAlerts = True
End Sub

You can also replace Thisworkbook with ActiveWorkbook or
Workbooks("Book1.xls") depending on what workbook does the sheets you want
to delete are located.

Regards,

Jon-jon

"andym" wrote in message
...

Can you tell me how to write code to delete all worksheets other than
those with a pre-specified name. eg all sheets other than Sheet1

Thanks


------------------------------------------------
~~ Message posted from http://www.ExcelTip.com/
~~View and post usenet messages directly from http://www.ExcelForum.com/

Thanks Jon-jon, that's perfec

-----------------------------------------------
~~ Message posted from http://www.ExcelTip.com
~~View and post usenet messages directly from http://www.ExcelForum.com

Glad to help

"andym" wrote in message
...

Thanks Jon-jon, that's perfect


------------------------------------------------
~~ Message posted from http://www.ExcelTip.com/
~~View and post usenet messages directly from http://www.ExcelForum.com/

Sorry, one more thing. How can I adapt the code to delete everythin
other than Sheets1 and 2? I have tried
If ws.Name < "Sheet1" Or "Sheet2" Then ws.Delete but this doesn'
work.

Thank

-----------------------------------------------
~~ Message posted from http://www.ExcelTip.com
~~View and post usenet messages directly from http://www.ExcelForum.com

if ws.name = "Sheet1" _
or ws.name = "Sheet2" then
'don't kill it
else
ws.delete
end if

or

if ws.name < "Sheet1" and ws.Name < "Sheet2" then ws.delete





andym wrote:

Sorry, one more thing. How can I adapt the code to delete everything
other than Sheets1 and 2? I have tried
If ws.Name < "Sheet1" Or "Sheet2" Then ws.Delete but this doesn't
work.

Thanks

------------------------------------------------
~~ Message posted from http://www.ExcelTip.com/
~~View and post usenet messages directly from http://www.ExcelForum.com/

--

Dave Peterson

Thank you

-----------------------------------------------
~~ Message posted from http://www.ExcelTip.com
~~View and post usenet messages directly from http://www.ExcelForum.com