Deleting Sheets

[MrT82]

Hi All,

Does anyone know of a macro that I can run that will delete all sheets in the workbook with the exception of a named sheet (that will always be constant)?

The problem I have that is I run a macro before this and therefore the number of sheets to delete always varies and when I recorded a macro to do this, it didn't like it!

Thanks,

Paul

 

[Andrew Poulsom]

Something like this:

Sub Test()
    Const SheetToKeep As String = "Master"
    Application.DisplayAlerts = False
    Dim Sh As Worksheet
    For Each Sh In ThisWorkbook.Worksheets
        If Sh.Name <> SheetToKeep Then Sh.Delete
    Next Sh
    Application.DisplayAlerts = False
End Sub

Change the SheetToKeep constant to suit.

 

[Oorang]

<hr>
<font face=Courier New><SPAN style="color:#00007F">Sub</SPAN> KillSheets()
<SPAN style="color:#00007F">Dim</SPAN> S <SPAN style="color:#00007F">As</SPAN> Worksheet
Application.DisplayAlerts = <SPAN style="color:#00007F">False</SPAN>
<SPAN style="color:#007F00">'Uses sheet codenames in case a user renames the sheet.</SPAN>
<SPAN style="color:#007F00">'Sheet code name is the name in the VBE that is NOT in</SPAN>
<SPAN style="color:#007F00">'Parentheses.</SPAN>
<SPAN style="color:#00007F">For</SPAN> <SPAN style="color:#00007F">Each</SPAN> S <SPAN style="color:#00007F">In</SPAN> Sheets
<SPAN style="color:#00007F">If</SPAN> S.CodeName <> "MySheet" <SPAN style="color:#00007F">Then</SPAN> S.Delete
<SPAN style="color:#00007F">Next</SPAN> S
Application.DisplayAlerts = <SPAN style="color:#00007F">True</SPAN>
<SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">Sub</SPAN>
</FONT>
<hr>

 

[MrT82]

Superb! Thank you!

 

[MrT82]

I thought I could tweak the above and below in order that it would select all sheets with the exception of sheet 1 (which is constant) however it doesn't appear to work. Any ideas?

Sub SelectAll()
Dim S As Worksheet
Application.DisplayAlerts = False
For Each S In Sheets
If S.CodeName <> "Sheet1" Then S.Select
Next S
Application.DisplayAlerts = True
End Sub

Thanks,

Paul

 

[Oorang]

That code is selecting each sheet. But it's selecting each sheet one at a time.
Try this
<hr>
Sub SelectAll()
Dim S As Worksheet
Application.DisplayAlerts = False
For Each S In Sheets
If S.CodeName <> "Sheet1" Then S.Select (False)
Next S
Application.DisplayAlerts = True
End Sub
<hr>