Deleting sheets with VBA

[Swaney]

I have a workbook with a master sheet ("Master"). If certain criteria are met, it creates sheets "1,2,and 3", but sometimes it may only create sheets "1 and 2" and so on.

My problem is that I want it to delete the existing sheets "1,2 and 3" when a button is pressed. This works fine if all three sheets have been created, but if they haven't I get an error that no sheet by that name exists.

How can I write a macro that will delete whatever sheets exist, but leave the master sheet alone?

 

[lenze]

Try this:

Sub DeleteWS()
For Each ws in Sheets
Application.DisplayAlerts=False
If ws.Name <> "Master" Then ws.delete
Next
Application.DisplayAlerts=True
End Sub

 

[Asala42]

How about:

Application.DisplayAlerts = False

For i = Sheets.Count To 2 Step -1
Sheets(i).Delete
Next i

Application.DisplayAlerts = True

 

[earlyd]

This is part of a routine that eliminated all sheets that had names like sheet1, sheet2,... The first statement tells you how many sheets you have:

SheetCount = Sheets.Count
For j = SheetCount To 1 Step -1
Sheets(j).Select
ExtraSheetName = ActiveSheet.Name
SheetFirst5 = Mid(ExtraSheetName, 1, 5)
If UCase(SheetFirst5) = "SHEET" Then

Application.DisplayAlerts = False
Sheets(ExtraSheetName).Delete
Application.DisplayAlerts = True
SheetsRemoved = SheetsRemoved + 1
Else
j = 1
End If
Next j

 

[Swaney]

Thanks Lenze that did the trick.

 

[kevin.philips]

Hi,

I have just sat here and cracked this for you - you won't believe how easy it is - you'll kick yourself.

Sub Button1_Click()
For Each Sheet In Application.Worksheets
If Sheet.Name <> "Master" Then
Sheet.Delete
End If
Next Sheet
End Sub

With this is does not matter where the master sheet is in your range of sheets, it will looop through all of the sheets - any problems send me a mail and I'll send you my example

All the best

Kevin

 

[kevin.philips]

Hi,

I have just sat here and cracked this for you - you won't believe how easy it is - you'll kick yourself.

Sub Button1_Click()
For Each Sheet In Application.Worksheets
If Sheet.Name <> "Master" Then
Sheet.Delete
End If
Next Sheet
End Sub

With this is does not matter where the master sheet is in your range of sheets, it will looop through all of the sheets - any problems send me a mail and I'll send you my example

All the best

Kevin

 

[kevin.philips]

Hi,

I have just sat here and cracked this for you - you won't believe how easy it is - you'll kick yourself.

Sub Button1_Click()
For Each Sheet In Application.Worksheets
If Sheet.Name <> "Master" Then
Sheet.Delete
End If
Next Sheet
End Sub

With this is does not matter where the master sheet is in your range of sheets, it will looop through all of the sheets - any problems send me a mail and I'll send you my example

All the best

Kevin

 

[Swaney]

Thank you all. They all seem to work very well. And Kevin, yes I am kicking myself.

 

[Karthik-Excelsior]

Thanks for this useful tip!