delete all but first 5 tabs in a workshhet

[ockie]

Hi,
on a worksheet I am trying to delete all but the first 5 tabs, named, "finish positions" " club champos" " committee" "master" "template"
The tabs after these 5 will regularly have different names and there could be up to 25 tabs that need deleting on a regular basis.

hoping someone can help, thanks in advance

 

[Snakehips]

@ockie Test this and see if it does the trick.
I have used the names rather than tab number, just in case.

Sub Del_Bar_5()

Response = MsgBox("Are yiu sure  you wish to delete tabs?", vbYesNo)

If Response = vbNo Then Exit Sub

Application.ScreenUpdating = False
Application.DisplayAlerts = False
For Each sht In ThisWorkbook.Sheets
    Select Case sht.Name
    Case "finish positions", "club champos", "committee", "master", "template"
    
    Case Else
    sht.Delete
    
    End Select
Next sht
Application.ScreenUpdating = True
Application.DisplayAlerts = True

End Sub

Hope that helps.

 

[ockie]

@ockie Test this and see if it does the trick.
I have used the names rather than tab number, just in case.

Sub Del_Bar_5()

Response = MsgBox("Are yiu sure  you wish to delete tabs?", vbYesNo)

If Response = vbNo Then Exit Sub

Application.ScreenUpdating = False
Application.DisplayAlerts = False
For Each sht In ThisWorkbook.Sheets
    Select Case sht.Name
    Case "finish positions", "club champos", "committee", "master", "template"
   
    Case Else
    sht.Delete
   
    End Select
Next sht
Application.ScreenUpdating = True
Application.DisplayAlerts = True

End Sub

Hope that helps.

Hi Snakehips, the code works too well, it has deleted "committee", "master", "template" as well as the ones it was supposed to. thanks for trying

 

[Snakehips]

Hi Snakehips, the code works too well, it has deleted "committee", "master", "template" as well as the ones it was supposed to. thanks for trying

Are the tab names as per the names in the code? Do your actual tab names have leading or trailing spaces????

 

[ockie]

Are the tab names as per the names in the code? Do your actual tab names have leading or trailing spaces????

Hi Snakehips, even from a distance you were able to pick up an error, the 3 tabs that were deleted had a capital letter in the name, I have changed them and IT WORKS.
thanks for your help, much appreciated.

 

[Fluff]

You can avoid problems with upper/lower case like

Select Case LCase(sht.Name)
Case "finish positions", "club champos", "committee", "master", "template"

 

[ockie]

You can avoid problems with upper/lower case like

Select Case LCase(sht.Name)
Case "finish positions", "club champos", "committee", "master", "template"

thanks Fluff, it works.

 

[Fluff]

Glad we could help & thanks for the feedback.