Delete Worksheets With Specified Prefix

[Samble]

Hi

I am a beginner at this but I guess this is probably an easy one!

I have several excel workbooks with many worksheets (over 500 in some). Around a third of these worksheets are named "Exp1", "Exp2", "Exp3", etc.

I would like a macro that will delete all worksheets that do not have names starting with the letters "Exp". I do not want any confirmation dialogue, and as the workbooks vary in size, I would like it to finish when there are no sheets left (except the Exp ones of course).

Any ideas?

Thanks

[norie]

Well for one workbook you could try this.

Sub DelExpShts() 
    Dim wb As Workbook 
    Dim ws As  Worksheet 
     
    Application.DisplayAlerts = False 
     
    Set wb =  ThisWorkbook 
     
    For Each ws In wb.Worksheets 
        Select  Case UCase(Left(ws. Name, 3)) 
        Case "EXP" 
            ws.Delete 
        Case Else 
             ' do nothing
        End Select 
    Next ws 
     
    Application.DisplayAlerts = True 
     
End Sub 

[Dave Hawley]

This code will open all the Workbooks in a specified folder & path;

Option Explicit 
 
Sub DeleteSheets() 
    Dim wbOpen As Workbook 
    Dim ws As  Worksheet 
     'Change Path
    Const strPath As String = "C:\Excel\" 
    Dim strExtension As String 
     
     'Comment out the 4 lines below to   debug
    Application. ScreenUpdating = False 
    Application.Calculation = xlCalculationManual 
    Application.DisplayAlerts = False 
    On  Error Resume Next 
     
    ChDir strPath 
     'Change extension
    strExtension = Dir("*.xls") 
     
     
     
    Do While strExtension <> "" 
        Set wbOpen = Workbooks.Open(strPath & strExtension) 
        For Each ws In wbOpen.Worksheets 
            If ws. Name Like "Exp*" Then ws.Delete 
        Next ws 
        strExtension = Dir 
     Loop 
     
    Application.ScreenUpdating = True 
    Application.Calculation = xlCalculationAutomatic 
    Application.DisplayAlerts = True 
    On Error Goto 0 
End Sub 

[Samble]

Thanks for the help - this works a treat.

The option to do this to all files in a folder is a huge timesaver too!