MrExcel Publishing
Your One Stop for Excel Tips & Solutions

Delete all Sheets except a couple specifically named


Posted by Mark Farr on October 18, 2001 2:24 PM

I am using a pivot table and when you drill down it opens a new sheet. Sheet1, sheet2 etc. I want to give an option to close all drill down sheets but I won't know exactly how many are open therefore will not know all the names. How can I close (delete) them all and not delete the two sheets that I need. Thanks for your help.


Posted by Mark Farr on October 18, 2001 6:31 PM

This is a VBA question. Thanks for your help

Posted by Norbert Stiles on October 19, 2001 1:12 AM


sheets that I need. Thanks for your help.


Not sure exactly what you want to do, but this will delete all sheets except sheets "Sheet1" and "Sheet2" :-

Dim ws As Worksheet
Application.DisplayAlerts = False
For Each ws In Worksheets
If ws.Name <> "Sheet1" And ws.Name <> "Sheet2" Then ws.Delete
Next
Application.DisplayAlerts = True

Posted by Mark Farr on October 19, 2001 8:14 AM

Thanks so much that worked great!